AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

[MySQL] 分组排序取前N条记录以及生成自动数字序列，类似group by后 limit

2015-07-08 2956
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 前言：        同事的业务场景是，按照cid、author分组，再按照id倒叙，取出前2条记录出来。
前言：
        同事的业务场景是，按照cid、author分组，再按照id倒叙，取出前2条记录出来。
        oracle里面可以通过row_number() OVER (PARTITION BY cid,author ORDER BY id DESC) 表示根据cid,author分组，在分组内部根据id排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)，而mysql数据库就没有这样的统计函数，需要自己写复杂的sql来实现。

1，录入测试数据
  1. USE csdn;
  2. DROP TABLE IF EXISTS test;
  3. CREATE TABLE test (
  4.   id INT PRIMARY KEY,
  5.   cid INT,
  6.   author VARCHAR(30)
  7. ) ENGINE=INNODB;


  10. INSERT INTO test VALUES 
  11. (1,1,\'test1\'),
  12. (2,1,\'test1\'),
  13. (3,1,\'test2\'),
  14. (4,1,\'test2\'),
  15. (5,1,\'test2\'),
  16. (6,1,\'test3\'),
  17. (7,1,\'test3\'),
  18. (8,1,\'test3\'),
  19. (9,1,\'test3\'),
  20. (10,2,\'test11\'),
  21. (11,2,\'test11\'),
  22. (12,2,\'test22\'),
  23. (13,2,\'test22\'),
  24. (14,2,\'test22\'),
  25. (15,2,\'test33\'),
  26. (16,2,\'test33\'),
  27. (17,2,\'test33\'),
  28. (18,2,\'test33\');
  29. INSERT INTO test VALUES (200,200,\'200test_nagios\');

2，原始的效率比较低下的子查询实现方式
SQL代码如下：

  1. SELECT * FROM test a 
  2. WHERE 
  3. N>(
  4.     SELECT COUNT(*) 
  5.     FROM test b
  6.     WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
  7. )ORDER BY cid,author,id DESC;

只要将N换成你要的数字比如2，就表示查询出每个分组的前2条记录，如下所示：
  1. mysql> SELECT * FROM test a 
  2.     -> WHERE 
  3.     -> 2>(
  4.     -> SELECT COUNT(*) 
  5.     -> FROM test b
  6.     -> WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
  7.     -> )ORDER BY cid,author,id DESC;
  8. +-----+------+----------------+
  9. | id | cid | author |
  10. +-----+------+----------------+
  11. | 2 | 1 | test1 |
  12. | 1 | 1 | test1 |
  13. | 5 | 1 | test2 |
  14. | 4 | 1 | test2 |
  15. | 9 | 1 | test3 |
  16. | 8 | 1 | test3 |
  17. | 11 | 2 | test11 |
  18. | 10 | 2 | test11 |
  19. | 14 | 2 | test22 |
  20. | 13 | 2 | test22 |
  21. | 18 | 2 | test33 |
  22. | 17 | 2 | test33 |
  23. | 200 | 200 | 200test_nagios |
  24. +-----+------+----------------+
  25. 13 ROWS IN SET (0.00 sec)


  28. mysql>


3，使用动态sql来实现
 先构造序列号码，引入一个@row来做rownumber
 SET @row=0;SET @mid='';SELECT cid, author, @row:=@row+1 rownum FROM test ORDER BY  cid, author LIMIT 10;   

序列号码已经出来了，再加一个@mid来进行分组，重点在于CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum，表示分组的时候会自动从1计数指导这个分组数据遍历结束。
SET @row=0;SET @mid='';SELECT cid, author,CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author FROM test ORDER BY cid,author DESC LIMIT 20;  

好了，再外面加一层inner JOIN 再对 rownumber 做限制 就可以拿到目标数据了。
 SET @row=0;
SET @mid='';
SELECT a.*,b.rownum FROM test a 
INNER JOIN (
SELECT cid, author, id, CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author MID 
FROM test 
ORDER BY cid,author,id DESC
) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id  WHERE b.rownum<3;  


执行结果如下所示：
  1. mysql> SET @row=0;
  2. QUERY OK, 0 ROWS affected (0.00 sec)


  5. mysql> SET @mid=\'\';
  6. QUERY OK, 0 ROWS affected (0.00 sec)


  9. mysql> SELECT a.*,b.rownum FROM test a 
  10.     -> INNER JOIN (
  11.     -> SELECT cid, author, id, CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author MID 
  12.     -> FROM test 
  13.     -> ORDER BY cid,author,id DESC
  14.     -> ) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id WHERE b.rownum<3; 
  15. +-----+------+----------------+--------+
  16. | id | cid | author | rownum |
  17. +-----+------+----------------+--------+
  18. | 2 | 1 | test1 | 1 |
  19. | 1 | 1 | test1 | 2 |
  20. | 5 | 1 | test2 | 1 |
  21. | 4 | 1 | test2 | 2 |
  22. | 9 | 1 | test3 | 1 |
  23. | 8 | 1 | test3 | 2 |
  24. | 11 | 2 | test11 | 1 |
  25. | 10 | 2 | test11 | 2 |
  26. | 14 | 2 | test22 | 1 |
  27. | 13 | 2 | test22 | 2 |
  28. | 18 | 2 | test33 | 1 |
  29. | 17 | 2 | test33 | 2 |
  30. | 200 | 200 | 200test_nagios | 1 |
  31. +-----+------+----------------+--------+
  32. 13 ROWS IN SET (0.01 sec)


  35. mysql>
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
SQL
Oracle
Serverless
数据库
关键词：
云数据库 RDS MySQL 版排序group
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
rudy_gao
目录
相关文章
技术自由圈/原疯狂创客圈
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
1221 0
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
蓝易云
|
存储 关系型数据库 MySQL
MySQL中实施排序(sorting)及分组(grouping)操作的技巧。
使用这些技巧时，需要根据实际的数据量、表的设计和服务器性能等因素来确定最合适的做法。通过反复测试和优化，可以得到最佳的查询性能。
蓝易云
501 0
游客kufrkwrbkmpsa
|
SQL Oracle 关系型数据库
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB（崖山数据库）中执行某些SQL语句时出现的报错问题，对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制，要求这些列必须出现在GROUP BY子句中，而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准，因此会报错。文章提供了两种解决方法：使用聚合函数处理非聚合列，或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出，SQL-92标准更为严谨合理，建议开发者遵循此规范以避免潜在问题。
游客kufrkwrbkmpsa
332 7
1639437117631681
|
SQL Oracle 关系型数据库
【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
在崖山数据库中执行某些 SQL 语句时出现报错（YAS-04316 not a single-group group function），而这些语句在 MySQL 中能成功执行。原因是崖山遵循 SQL-92 标准，不允许选择列表中包含未在 GROUP BY 子句中指定的非聚合列，而 MySQL 默认允许这种操作。解决办法包括：使用聚合函数处理非聚合列或拆分查询为两层，先进行 GROUP BY 再排序。总结来说，SQL-92 更严格，确保数据一致性，MySQL 在 5.7 及以上版本也默认遵循此标准。
1639437117631681
370 1
运营研究坊
|
SQL 缓存 关系型数据库
MySQL Limit实现原理
本文深入解析了MySQL中`LIMIT`子句的实现原理及其在分页、性能优化等场景下的应用技巧。文章详细介绍了`LIMIT`的基本语法、MySQL内部处理流程，以及如何通过索引优化、覆盖索引等策略提升分页查询的性能，并提供了实践建议。
运营研究坊
1228 3
盛透侧视攻城狮
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
盛透侧视攻城狮
654 5
刘大猫.
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
刘大猫.
843 1
猿java
|
SQL 搜索推荐 关系型数据库
MySQL 如何实现 ORDER BY 排序?
本文详细解析了MySQL中`ORDER BY`的实现原理及优化方法。通过解析与优化、执行及多种优化技术，如索引利用、内存排序、外部排序等，帮助你提升排序性能。了解其背后的机制，可显著优化查询效率。
猿java
1009 4
猿java
|
SQL 缓存 关系型数据库
MySQL Limit实现原理
本文详细探讨了MySQL中`LIMIT`子句的实现原理及其在不同场景下的应用。`LIMIT`用于控制查询结果的行数，结合`OFFSET`可实现分页查询。其内部实现涉及解析器、优化器和执行器三部分，通过索引利用、子查询优化等提升性能。文章还提供了性能优化策略，如索引优化、覆盖索引及延迟关联等，并给出实践建议。
猿java
611 4
猿java
|
SQL 搜索推荐 关系型数据库
MySQL 如何实现 ORDER BY 排序?
在实际开发中，我们经常会使用 MySQL 的 `ORDER BY`进行排序，那么，`ORDER BY`是如何实现的排序的？我们该如何优化 `ORDER BY`的排序性能？这篇文章，我们来聊一聊。
猿java
290 4

热门文章

最新文章

  • 1
    阿里云DTS踩坑经验分享系列｜如何使用DTS进行MySQL->ClickHouse同步
  • 2
    MySQL怎样删除重复数据，只保留一条？
  • 3
    MySQL主从同步配置
  • 4
    PostgreSQL\MySQL比较
  • 5
    mysql 最新版5.7.18免安装配置指南（不是exe哦，下载完后没有data目录）
  • 6
    mySQL 使用通配符模糊查询
  • 7
    【阿里云新品发布·周刊】第11期：云数据库 MySQL 8.0 重磅发布，更适合企业使用场景的RDS数据库
  • 8
    mysql 状态访问方法
  • 9
    Java必学MySQL数据库应用场景
  • 10
    Mysql笔记--常用命令
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    1143
  • 2
    MySQL中日期和时间函数的使用指南
    516
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    971
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    492
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    942
  • 6
    MySQL源码编译安装
    2336
  • 7
    MySQL二进制包安装
    1644
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    915
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    914
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    3757

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！