mysql分组排序问题

• 1.建表：

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;  

• 2.插入数据

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);  

• 部分聚合函数使用

where 在聚合前过滤数据
having在聚合后筛选数据，having使用在group by 之后，order by之前
group by x,y:先以字段x分组，然后在x分组的基础上再按照y分组
误区：使用group by查询的字段，只能是group by函数所跟的字段，取其它字段不会报错，但取的是聚合后的第一个字段，该字段可能是无效字段

• 4.实现1：

SELECT
   a.* 
FROM
   mygoods a
   LEFT JOIN mygoods b ON a.cat_id = b.cat_id 
   AND a.price <= b.price 
GROUP BY
   a.cat_id,
   a.price 
HAVING
   count( a.goods_id ) <= 2
ORDER BY
   a.cat_id,
   a.price DESC;

• 拆析1：以cat_id 分组

    SELECT cat_id ,count(cat_id) from mygoods GROUP BY cat_id ;

• 拆析2：当以cat_id 作为关联映射字段，根据笛卡尔积，会形成5*5+5*5+4*4+3*3+3*3=84条数据，即以cat_id为101的会有5条相同的数据，其它类推

SELECT
  a.* 
FROM
  mygoods a
  LEFT JOIN mygoods b ON a.cat_id = b.cat_id 

• 拆析3：先以cat_id分组，再以price分组，假如商品的某个cat_id中有10个price，如果他是价格最高的，那么 <= 最高价格的数据应该 <=1，需要N条同理

SELECT
   a.* 
FROM
   mygoods a
   LEFT JOIN mygoods b ON a.cat_id = b.cat_id 
   AND a.price <= b.price 
GROUP BY
   a.cat_id,
   a.price 
HAVING
   count( a.goods_id ) <= 2

• 4.实现2：
• 思路：取一条数据判断在同一类别中，比它价格低的数据有<=N

SELECT
   a.* 
FROM
   mygoods a 
WHERE
   ( SELECT count( * ) FROM mygoods b WHERE a.cat_id = b.cat_id AND a.price <= b.price ) <= 2 
ORDER BY
   a.cat_id,
   a.price DESC;