SQL面试题（二）

问题：

购物人      商品名称     数量
A            甲          2
B            乙          4
C            丙          1
A            丁          2
B            丙          5

给出所有购入商品为两种或两种以上的购物人记录

 

表结构：

DROP TABLE IF EXISTS `buyrecord`;
CREATE TABLE `buyrecord` (
  `Id` int(11) NOT NULL auto_increment,
  `name` varchar(12) default NULL,
  `things` varchar(11) default NULL,
  `info` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
#
# Dumping data for table buyrecord
#
/*!40101 SET NAMES utf8 */;
INSERT INTO `buyrecord` VALUES (1,'A','CODE1',2111);
INSERT INTO `buyrecord` VALUES (2,'B','CODE2',41111);
INSERT INTO `buyrecord` VALUES (3,'C','CODE3',11111);
INSERT INTO `buyrecord` VALUES (4,'A','CODE4',211);
INSERT INTO `buyrecord` VALUES (5,'B','CODE3',5112);
INSERT INTO `buyrecord` VALUES (6,'D','CODE5',133);
INSERT INTO `buyrecord` VALUES (7,'D','CODE5',345);

 

方法1：

SELECT s.name, s.things, s.info FROM buyrecord AS s, (SELECT name, COUNT(DISTINCT things) as tcout FROM buyrecord GROUP BY name) AS t
WHERE t.tcout > 1 AND t.name = s.name ORDER BY name;

方法2：

SELECT s.name, s.things, s.info FROM buyrecord AS s WHERE s.name IN (
SELECT a.name FROM buyrecord AS a GROUP BY a.name HAVING COUNT(DISTINCT a.things) >= 2);