Mysql分类排序sql

今天在生产环境遇到一个要求用sql统计某分类下产品个数排名的问题，记录如下：

为屏蔽真实产品数据，用地域分类表及部分数据做记录。

一、创建表结构

CREATE TABLE `area_dic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '地区名称',
  `fid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='地域表';

二、填充数据

INSERT INTO `area_dic` VALUES (1, '北京市', 0);
INSERT INTO `area_dic` VALUES (2, '上海市', 0);
INSERT INTO `area_dic` VALUES (3, '天津市', 0);
INSERT INTO `area_dic` VALUES (4, '重庆市', 0);
INSERT INTO `area_dic` VALUES (5, '广东省', 0);
INSERT INTO `area_dic` VALUES (6, '福建省', 0);
......
INSERT INTO `area_dic` VALUES (102, '西城区', 1);
INSERT INTO `area_dic` VALUES (104, '宣武区', 1);
INSERT INTO `area_dic` VALUES (105, '朝阳区', 1);
INSERT INTO `area_dic` VALUES (106, '海淀区', 1);
......
INSERT INTO `area_dic` VALUES (201, '黄浦区', 2);
INSERT INTO `area_dic` VALUES (202, '卢湾区', 2);
INSERT INTO `area_dic` VALUES (203, '徐汇区', 2);
INSERT INTO `area_dic` VALUES (204, '长宁区', 2);
......

限于篇幅，只填充部分数据。

三、开始查询

查询排序语句如下：

SET @row_num := 0;
select 
T.name,
T.cnt,
@row_num := @row_num + 1 as row_num
from (
SELECT
	a.`name`,
	count(*) as cnt
FROM
	area_dic AS a
INNER JOIN area_dic AS b ON b.fid = a.id
GROUP BY
	a.`id`
ORDER BY cnt DESC) as T;

-- 或者sql改写如下：

SET @row_num := 0;
select 
T.name,
T.cnt,
@row_num as row_num
from (
SELECT
	a.`name`,
	count(*) as cnt
FROM
	area_dic AS a
INNER JOIN area_dic AS b ON b.fid = a.id
GROUP BY
	a.`id`
ORDER BY cnt DESC) as T
ORDER BY @row_num, LEAST(0,@row_num := @row_num + 1) ASC;

语句解析：利用子查询，将需要的数据分组、排序好，在上层查询中，利用自定义变量@row_num，每行加1，实现排序，查询结果如下图。

分析查询结果发现，“伊春市”和“保定市”，下属的行政区域都是23个，但是他们的排名却是2和3，我们想实现的是这两个市并列第二，我们来改写sql。

SET @row_num := 0;
SET @prev_cnt := 0;
SET @rank := 0;
select 
T.name,
T.cnt,
@row_num := @row_num + 1 as row_num,
@rank := IF(@prev_cnt != cnt, @rank + 1, @rank) as rank,
@prev_cnt := cnt as dummy
from (
SELECT
	a.`name`,
	count(*) as cnt
FROM
	area_dic AS a
INNER JOIN area_dic AS b ON b.fid = a.id
GROUP BY
	a.`id`
ORDER BY cnt DESC) as T;

 语句解析：利用自定义变量@prev_cnt记录本条数据的cnt，在下一条数据时候，判断 @prev_cnt和下一条数据的cnt是否相等，只有在不相等的时候，才给自定义变量@rank加1。

查询结果如下图：

 “伊春市”和“保定市”，但是他们的排名都是2了。