加载中…mysql分组取每组前几条记录(排序)
(2013-04-01 14:55:43)
标签：
groupbyorderby分组排序 |
分类： 菜鸟DBA之MySQL |
测试数据
[sql] view plaincopy
-
CREATE
TABLE `mygoods` ( -
`goods_id` int(11) unsigned NOTNULL 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)
参考地址：
http://www.cnblogs.com/mo-beifeng/archive/2012/03/26/2341886.html
喜欢
0
赠金笔
前一篇：perl 5.10.0 安装
后一篇：【转】Perl脚本的调试方法