关闭

Mysql分组统计、排序、取前N条记录解决方案

3030人阅读 评论(0) 收藏 举报

今日根据项目需求,需要在mysql中解决记录的分组统计、排序,并抽取前10条记录的功能。现已解决,解决方案如下:

1)表结构

<span style="font-size:18px;">CREATE TABLE `policy_keywords_rel` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `content_id` int(11) NOT NULL COMMENT '文章id',
  `keyword_id` int(11) NOT NULL COMMENT '关键词id',
  `cnt` int(11) NOT NULL COMMENT '关键词频次',
  `n` varchar(10) DEFAULT NULL,
  `keyword` varchar(90) DEFAULT NULL COMMENT '关键词名称',
  PRIMARY KEY (`content_id`,`keyword_id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章与关键词关系表'</span>


原始表部分数据截图如下所示:



2)实现功能

根据字段‘n’(关键词类别)和‘keyword_id’(关键词id)进行分组统计每个类别下相同关键词的总频次('cnt'为关键词的频次),然后根据关键词总频次进行降序排序,最后抽取每个分组下总频次排名前10的关键词。


3)解决方案

<span style="font-size:18px;">SET @row=0; SET @mid= ''; 
SELECT T2.* FROM (SELECT T.*,case WHEN @mid=n THEN @row:=@row+1 ELSE @row:=1 END rownum,@mid:=n mid FROM (SELECT keyword_id,keyword,sum(cnt),n FROM policy_keywords_rel  
GROUP BY keyword_id, n ORDER BY n,sum(cnt) DESC) AS T ) AS T2
WHERE T2.rownum<=10</span>


解决思路:变量@row用来做rownum,变量@mid用来分组。最里层的sql语句通过group by与order by语句获取分组统计好的数据集,即根据关键词类别及关键词进行分组、统计所有文章相同在每个类别下相同关键词的总频次;中间层的sql语句获取每组分类的rownum,当mid的值与分组字段n的值相同是rownum自增,若不相同则从1开始计数;最外层的sql语句根据rownum抽取每组分类的记录个数。


运行结果如下所示:


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:29541次
    • 积分:486
    • 等级:
    • 排名:千里之外
    • 原创:20篇
    • 转载:6篇
    • 译文:0篇
    • 评论:3条
    文章分类