SQL整理七

图书信息表：books
+------+---------+
bookid  bookname |  
| 1        a     |
| 2        b     |
| 3        v     |
| 4        d     |
| 5        f     | 
| 6        g     |
| 7        h     | 
| 8        j     | 
| 9        k     | 
| 10       l     | 
| 11       r     | 
| 12       t     | 
+------+---------+
SQL：
+-----------------------------------------+
DROP TABLE IF EXISTS `books`;   
CREATE TABLE `books` (   
  `bookid` int(11) NOT NULL default '0',   
  `bookname` varchar(12) default NULL,   
  PRIMARY KEY  (`bookid`)   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;   
 
INSERT INTO `books` VALUES (1,'a');   
INSERT INTO `books` VALUES (2,'b');   
INSERT INTO `books` VALUES (3,'v');   
INSERT INTO `books` VALUES (4,'d');   
INSERT INTO `books` VALUES (5,'f');   
INSERT INTO `books` VALUES (6,'g');   
INSERT INTO `books` VALUES (7,'h');   
INSERT INTO `books` VALUES (8,'j');   
INSERT INTO `books` VALUES (9,'k');   
INSERT INTO `books` VALUES (10,'l');   
INSERT INTO `books` VALUES (11,'r');   
INSERT INTO `books` VALUES (12,'t');  
+-----------------------------------------+

图书借阅信息表：checkout
+--------------+-------+----------+
|Id  readername  bookid  checktime|  
| 1  zhang       12       23      |
| 2   li         9        30      | 
| 3   zhang      7        2       |
| 4   hu         11       52      | 
| 5   li         10       12      | 
| 6   zhang      2        30      | 
| 7   zhang      3        45      | 
| 8   zhang      4        12      | 
| 9   wang       6        30      |
| 10  li         8        114     | 
| 11  li         4        50      | 
+--------------+-------+----------+
SQL：
+------------------------------------------------+
DROP TABLE IF EXISTS `checkout`;   
CREATE TABLE `checkout` (   
  `Id` int(11) NOT NULL auto_increment,   
  `readername` varchar(12) default NULL,   
  `bookid` int(11) default NULL,   
  `checktime` int(11) default NULL,   
  PRIMARY KEY  (`Id`)   
) ENGINE=MyISAM DEFAULT CHARSET=utf8;   
   
INSERT INTO `checkout` VALUES (1,'zhang',12,23);   
INSERT INTO `checkout` VALUES (2,'li',9,30);   
INSERT INTO `checkout` VALUES (3,'zhang',7,2);   
INSERT INTO `checkout` VALUES (4,'hu',11,52);   
INSERT INTO `checkout` VALUES (5,'li',10,12);   
INSERT INTO `checkout` VALUES (6,'zhang',2,30);   
INSERT INTO `checkout` VALUES (7,'zhang',3,45);   
INSERT INTO `checkout` VALUES (8,'zhang',4,12);   
INSERT INTO `checkout` VALUES (9,'wang',6,30);   
INSERT INTO `checkout` VALUES (10,'li',8,114); 
INSERT INTO `checkout` VALUES (11,'li',4,50); 
+------------------------------------------------+
要求：
1.每个人借阅期限是30天。求出过期书大于2本的读者的名字，书名和已借阅时间。
+---------------------------------------------+
|SELECT b.readername, a.bookname, b.checktime |
|FROM books a, checkout b, (                  |
| SELECT readername, count( readername ) AS ct|
| FROM checkout                               |
| WHERE checktime >30                         |
| GROUP BY readername                         |
| HAVING ct >=2                               |
|)c                                           |
|WHERE a.bookid = b.bookid                    |
|AND c.readername = b.readername              |
|AND b.checktime >30                          |
+---------------------------------------------+