SQL整理三

产品信息表：product
+--+-----+-------+------+
|id| name| p_name| p_num| 
+--+-----+-------+------+
|1 |  A  | 商品甲 |  2   | 
+--+-----+-------+------+
|2 |  B  | 商品乙 |  4   | 
+--+-----+-------+------+
|3 |  C  | 商品丙 |  1   |  
+--+-----+-------+------+
|4 |  A  | 商品丁 |  2   | 
+--+-----+-------+------+
|5 |  B  | 商品丙 |  5   |   
+--+-----+-------+------+
结果：     
+----+-------+------+
|name| p_name|p_num |
+----+-------+------+
| A  | 商品甲 | 2    |
+----+-------+------+
| B  | 商品乙 | 4    |
+----+-------+------+
| A  | 商品丁 | 2    |
+----+-------+------+
| B  | 商品丙 | 5    |
+----+-------+------+
SQL语句：
+------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `p_name` varchar(20) default NULL,
  `p_num` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk AUTO_INCREMENT=6 ;

INSERT INTO `product` VALUES (1, 'A', '商品甲', 2);
INSERT INTO `product` VALUES (2, 'B', '商品乙', 4);
INSERT INTO `product` VALUES (3, 'C', '商品丙', 1);
INSERT INTO `product` VALUES (4, 'A', '商品丁', 2);
INSERT INTO `product` VALUES (5, 'B', '商品丙', 5);
+------------------------------------------------------+
要求：购买至少俩种商品的顾客，并列出其姓名，购买的商品，购买数量
方法一：
+---------------------------------+
|SELECT a.name,a.p_name,a.p_num   |
|FROM product a,(                 |
| select name,count(p_name) as ct | 
| from product                    |
| group by name) b                | 
|where a.name=b.name and b.ct>=2  |
+---------------------------------+
方法二：
+---------------------------------+
|SELECT a.name, a.p_name, a.p_num |
|FROM product a                   |
|WHERE a.name                     |
|IN (                             |
| SELECT name                     |
| FROM product                    |
| GROUP BY name                   |
| HAVING count(DISTINCT p_name)>=2|
| )                               |
+---------------------------------+