- CREATE
TABLE `mygoods` ( -
`goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, -
`cat_id` int(11) NOT NULL DEFAULT '0', -
`price` tinyint(3) NOT NULL DEFAULT '0', -
`status` tinyint(3) DEFAULT '1', -
PRIMARY KEY (`goods_id`), -
KEY `icatid` (`cat_id`) - )
ENGINE=InnoDB DEFAULT CHARSET=utf8; -
- INSERT
INTO `mygoods` VALUES (1, 101, 90, 0); - INSERT
INTO `mygoods` VALUES (2, 101, 99, 1); - INSERT
INTO `mygoods` VALUES (3, 102, 98, 0); - INSERT
INTO `mygoods` VALUES (4, 103, 96, 0); - INSERT
INTO `mygoods` VALUES (5, 102, 95, 0); - INSERT
INTO `mygoods` VALUES (6, 102, 94, 1); - INSERT
INTO `mygoods` VALUES (7, 102, 93, 1); - INSERT
INTO `mygoods` VALUES (8, 103, 99, 1); - INSERT
INTO `mygoods` VALUES (9, 103, 98, 1); - INSERT
INTO `mygoods` VALUES (10, 103, 97, 1); - INSERT
INTO `mygoods` VALUES (11, 104, 96, 1); - INSERT
INTO `mygoods` VALUES (12, 104, 95, 1); - INSERT
INTO `mygoods` VALUES (13, 104, 94, 1); - INSERT
INTO `mygoods` VALUES (15, 101, 92, 1); - INSERT
INTO `mygoods` VALUES (16, 101, 93, 1); - INSERT
INTO `mygoods` VALUES (17, 101, 94, 0); - INSERT
INTO `mygoods` VALUES (18, 102, 99, 1); - INSERT
INTO `mygoods` VALUES (19, 105, 85, 1); - INSERT
INTO `mygoods` VALUES (20, 105, 89, 0); - INSERT
INTO `mygoods` VALUES (21, 105, 99, 1);
说明:
表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。
需求:每个分类下,找出两个价格最高的有效的商品。
1.每个分类找出价格最高的两个商品
mysql> select a.*
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|
2 |
101 |
99 |
1 |
|
17 |
101 |
94 |
0 |
|
18 |
102 |
99 |
1 |
|
3 |
102 |
98 |
0 |
|
8 |
103 |
99 |
1 |
|
9 |
103 |
98 |
1 |
|
11 |
104 |
96 |
1 |
|
12 |
104 |
95 |
1 |
|
21 |
105 |
99 |
1 |
|
20 |
105 |
89 |
0 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
2.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.*
-> from mygoods a
-> where (select count(*) from mygoods
-> where cat_id
= a.cat_id and price > a.price and status=1
) <2
-> and status=1
-> order by a.cat_id,a.price desc ;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|
2 |
101 |
99 |
1 |
|
16 |
101 |
93 |
1 |
|
18 |
102 |
99 |
1 |
|
6 |
102 |
94 |
1 |
|
8 |
103 |
99 |
1 |
|
9 |
103 |
98 |
1 |
|
11 |
104 |
96 |
1 |
|
12 |
104 |
95 |
1 |
|
21 |
105 |
99 |
1 |
|
19 |
105 |
85 |
1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
3.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.*
-> from mygoods a
-> left join mygoods b
-> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
-> where a.status=1
-> group by a.goods_id,a.cat_id,a.price
-> having count(b.goods_id) < 2
-> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|
2 |
101 |
99 |
1 |
|
16 |
101 |
93 |
1 |
|
18 |
102 |
99 |
1 |
|
6 |
102 |
94 |
1 |
|
8 |
103 |
99 |
1 |
|
9 |
103 |
98 |
1 |
|
11 |
104 |
96 |
1 |
|
12 |
104 |
95 |
1 |
|
21 |
105 |
99 |
1 |
|
19 |
105 |
85 |
1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品(错误)
mysql> select a.*
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|
2 |
101 |
99 |
1 |
|
18 |
102 |
99 |
1 |
|
8 |
103 |
99 |
1 |
|
9 |
103 |
98 |
1 |
|
11 |
104 |
96 |
1 |
|
12 |
104 |
95 |
1 |
|
21 |
105 |
99 |
1 |
+----------+--------+-------+--------+
7 rows in set (0.00 sec)
由上可知,如果需要增加条件的话,需要在两处增加条件。
mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+--------+------------------------+
| cat_id | GROUP_CONCAT(goods_id) |
+--------+------------------------+
|
101 | 1,2,15,16,17
|
|
102 | 3,5,6,7,18
|
|
103 | 4,8,9,10
|
|
104 | 11,12,13
|
|
105 | 19,20,21
|
+--------+------------------------+
5 rows in set (0.00 sec)