SQL面试题（五）

问题：

    图书馆管理系统中，有2个表.book表包括book_id, book_name字段. 借阅关系表checkout表包括三个字段reader_name,book_id,days(已借阅时间)。每个人借阅期限是30天。求出过期书大于2本的读者的名字，书名和已借阅时间。

 

建立表：

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;

#
# Dumping data for table books
#
/*!40101 SET NAMES 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');

 

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;

#
# Dumping data for table checkout
#

/*!40101 SET NAMES 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);

 

 

 

解决方法：

 

 

 

SELECT * FROM checkout;

 

SELECT b.readername, a.bookname,b.checktime FROM books AS a, checkout AS b,

(SELECT readername, COUNT(checktime) FROM checkout WHERE checktime >= 30 GROUP BY readername 

HAVING COUNT(checktime) >= 2) AS c 

WHERE a.bookid = b.bookid AND b.readername = c.readername AND b.checktime >= 30;