问题:
购物人 商品名称 数量
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);