mysql 重用技巧

最新推荐文章于 2022-07-28 15:47:48 发布
最新推荐文章于 2022-07-28 15:47:48 发布
阅读量384 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lxf0613050210/article/details/50615234
版权
一：分组排序

同事提了一个需求，要求按照某列分组，然后将各组的前几条抽取出来。

表结构

  1. CREATE TABLE `total_freq_ctrl` (  
  2.   `time` int(10) unsigned NOT NULL,  
  3.   `machine` char(64) NOT NULL,  
  4.   `module` char(32) NOT NULL,  
  5.   `total_flow` int(10) unsigned NOT NULL,  
  6.   `deny_flow` int(10) unsigned NOT NULL,  
  7.   PRIMARY KEY (`module`,`machine`,`time`)  
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

tudou@b2c.xiaomi.com

原sql

  1. SELECT machine, deny_flow, total_flow, time FROM total_freq_ctrl A WHERE 1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) AND A.module = 'all' ORDER BY A.time desc;  

只要将 1 改成N就变成取每组的前N条，因为我极其不喜欢子查询，就改就尝试改称join 的方式。

不过这里需要对所有数据进行排序才能确定每组的前N条，所以最佳优化也要全表扫描一次。

首先我要对表中数据进行排序，引入一个变量@row来做rownumber

  1. set @row=0;set @mid='';SELECT module, machine, time, @row:=@row+1 rownum FROM total_freq_ctrl order by module,machine,time desc limit 10;   
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. Query OK, 0 rows affected (0.00 sec)  
  5.   
  6. +--------+---------------+------------+--------+  
  7. | module | machine       | time       | rownum |  
  8. +--------+---------------+------------+--------+  
  9. | all    | 10.201.20.181 | 1409640060 |      1 |  
  10. | all    | 10.201.20.181 | 1409640000 |      2 |  
  11. | all    | 10.201.20.181 | 1409639940 |      3 |  
  12. | all    | 10.201.20.181 | 1409639880 |      4 |  
  13. | all    | 10.201.20.97  | 1409640060 |      5 |  
  14. | all    | 10.201.20.97  | 1409640000 |      6 |  
  15. | all    | 10.201.20.97  | 1409639940 |      7 |  
  16. | all    | 10.201.20.97  | 1409639880 |      8 |  
  17. | all    | 10.201.20.98  | 1409640060 |      9 |  
  18. | all    | 10.201.20.98  | 1409640000 |     10 |  
  19. +--------+---------------+------------+--------+  

rownumber已经出来了，再加一个@mid来进行分组
  1. set @row=0;set @mid='';SELECT module, machine, time,case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine FROM total_freq_ctrl order by module,machine,time desc limit 20;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. Query OK, 0 rows affected (0.00 sec)  
  5.   
  6. +--------+---------------+------------+--------+---------------+  
  7. | module | machine       | time       | rownum | @mid:=machine |  
  8. +--------+---------------+------------+--------+---------------+  
  9. | all    | 10.201.20.181 | 1409640180 |      1 | 10.201.20.181 |  
  10. | all    | 10.201.20.181 | 1409640120 |      2 | 10.201.20.181 |  
  11. | all    | 10.201.20.181 | 1409640060 |      3 | 10.201.20.181 |  
  12. | all    | 10.201.20.181 | 1409640000 |      4 | 10.201.20.181 |  
  13. | all    | 10.201.20.181 | 1409639940 |      5 | 10.201.20.181 |  
  14. | all    | 10.201.20.181 | 1409639880 |      6 | 10.201.20.181 |  
  15. | all    | 10.201.20.97  | 1409640180 |      1 | 10.201.20.97  |  
  16. | all    | 10.201.20.97  | 1409640120 |      2 | 10.201.20.97  |  
  17. | all    | 10.201.20.97  | 1409640060 |      3 | 10.201.20.97  |  
  18. | all    | 10.201.20.97  | 1409640000 |      4 | 10.201.20.97  |  
  19. | all    | 10.201.20.97  | 1409639940 |      5 | 10.201.20.97  |  
  20. | all    | 10.201.20.97  | 1409639880 |      6 | 10.201.20.97  |  
  21. | all    | 10.201.20.98  | 1409640180 |      1 | 10.201.20.98  |  
  22. | all    | 10.201.20.98  | 1409640120 |      2 | 10.201.20.98  |  
  23. | all    | 10.201.20.98  | 1409640060 |      3 | 10.201.20.98  |  
  24. | all    | 10.201.20.98  | 1409640000 |      4 | 10.201.20.98  |  
  25. | all    | 10.201.20.98  | 1409639940 |      5 | 10.201.20.98  |  
  26. | all    | 10.201.20.98  | 1409639880 |      6 | 10.201.20.98  |  
  27. +--------+---------------+------------+--------+---------------+  

好了，再外面加一层inner join 再对 rownumber 做限制 就可以拿到目标数据了。

tudou@b2c.xiaomi.com

  1. set @row=0;set @mid='';select a.*,b.rownum from total_freq_ctrl a inner join (SELECT module, machine, time, case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine mid FROM total_freq_ctrl order by module,machine,time desc) b on b.module=a.module and b.machine=a.machine and b.time=a.time where b.rownum<5;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. Query OK, 0 rows affected (0.00 sec)  
  5.   
  6. +------------+---------------+--------+------------+-----------+--------+  
  7. | time       | machine       | module | total_flow | deny_flow | rownum |  
  8. +------------+---------------+--------+------------+-----------+--------+  
  9. | 1409640360 | 10.201.20.181 | all    |      53937 |      6058 |      1 |  
  10. | 1409640300 | 10.201.20.181 | all    |      52588 |      5701 |      2 |  
  11. | 1409640240 | 10.201.20.181 | all    |      54254 |      5608 |      3 |  
  12. | 1409640180 | 10.201.20.181 | all    |      54684 |      5811 |      4 |  
  13. | 1409640360 | 10.201.20.97  | all    |      50679 |      5307 |      1 |  
  14. | 1409640300 | 10.201.20.97  | all    |      50472 |      5239 |      2 |  
  15. | 1409640240 | 10.201.20.97  | all    |      51586 |      5509 |      3 |  
  16. | 1409640180 | 10.201.20.97  | all    |      50794 |      5378 |      4 |  
  17. | 1409640360 | 10.201.20.98  | all    |      84747 |      5652 |      1 |  
  18. | 1409640300 | 10.201.20.98  | all    |      84506 |      5696 |      2 |  
  19. | 1409640240 | 10.201.20.98  | all    |      84982 |      5513 |      3 |  
  20. | 1409640180 | 10.201.20.98  | all    |      83997 |      5623 |      4 |  
  21. +------------+---------------+--------+------------+-----------+--------+ 

lxf0613050210
关注
专栏目录
MySQL中的ROWNUM的实现
ACMAIN_CHM的专栏
04-20 8万+
MySQL 几乎模拟了 Oracle,SQL Server等商业数据库的大部分功能，函数。但很可惜，到目前的版本(5.1.33)为止,仍没有实现ROWNUM这个功能。 下面介绍几种具体的实现方法.建立实验环境如下mysql> create table tbl (    ->  id      int primary key,    ->  col     int    -> );Que
mysql的rownum函数_MySQL中的ROWNUM的实现_MySQL
weixin_42357719的博客
02-02 1794
MySQL 几乎模拟了 Oracle,SQL Server等商业数据库的大部分功能，函数。但很可惜，到目前的版本(5.1.33)为止,仍没有实现ROWNUM这个功能。下面介绍几种具体的实现方法.建立实验环境如下mysql> create table tbl (-> id int primary key,-> col int-> );Query OK, ...
Mysql实现rownum
weixin_33964094的博客
03-30 98
经过查找，在网上找到了类似的写法。但是经过测试，发现查询结果与期望的情况不相符，经过一系列的测试，终于发现了问题所在。 以下是经过测试过的sql select id,class,score,rank from ( select b.id,b.class,b.score, if(@pdept=b.class,@rank:=@rank+1,@rank:=1) as rank, ...
mysql重用命令
cpnh6716的博客
11-24 94
一、连接MYSQL。 格式： mysql -h主机地址 -u用户名－p用户密码 1、例1：连接到本机上的MYSQL。 首先在打开DOS窗口，然后进入目录 mysqlbin，再键入命令mysql -u root -p，回...
mysql 实现rownum_MySQL 中实现 rownum
weixin_39819283的博客
01-21 812
在oracle中,可使用rownum处理很多与行号相关的问题,但是mysql中没有类似的函数。本文将介绍mysql中实现行号的方法[emailprotected]_name，其中变量名var_name可以由当前字符集的文字数字字符 , ‘.’ , ‘_’ 和 ‘$’ 组成。 默认字符集是cp1252 (Latin1)。可以用mysqld的–default-character-set选项更改字符集...
Mysql使用技巧
weixin_44278368的博客
07-16 115
Mysql查询排序新的改变常见函数LENGTH函数concat 函数uppersubstr/substring 截取字符串instr 返回起始索引trimlpad，rpadrelpace 替换字符串数学函数round 四舍五入ceil 向上取整，大于等于参数的最小参数floor 向下取整，小于等于参数的最大参数TRUNCATE 截断mod 取余日期函数nowcurdata 返回日期，没有时间 curtime返回当前时间，没有日期date_format日期转字符串其他函数流程控制函数if 语句选择判读cas
mysql高级操作_mysql高级使用和技巧
weixin_28725959的博客
01-19 227
文章首发于公众号:松花皮蛋的黑板报作者就职于京东,在稳定性保障、敏捷开发、高级JAVA、微服务架构有深入的理解一、普通索引和唯一索引查询上来说，普通索引查找到满足条件的记录后会接着查找下一个记录(innodb的数据是按页读写的)，判断是否满足。然而唯一索引是查询到了就立即返回了。所以如果你明确知道只有一条结果则应该加上limit 1更新上来说，普通索引会用到charge buffer优化，将更新操...
Mysql根据时间查询日期的优化技巧
12-15
在MySQL数据库中，查询日期相关的数据是常见的操作，尤其是在用户行为分析、日志记录等场景。本文将深入探讨如何优化...在实际工作中，可以根据具体需求和数据库设计灵活运用这些技巧，确保系统的稳定运行和高效响应。
MySQL数据库常用操作技巧总结
12-16
以下是一些MySQL数据库的重要操作技巧： 一、查询不同表中同名字段 在处理多个表时，我们可能需要找到所有具有相同字段名的表。通过`information_schema.columns`系统表，我们可以方便地进行此操作。使用`SELECT * ...
JAVA连接MySQL数据库的方法与技巧的研究.pdf
10-10
总的来说，理解和掌握Java连接MySQL数据库的方法与技巧，是成为一名合格的Java开发者必备的基础能力。通过合理利用JDBC API，结合良好的编程习惯和设计模式，可以有效地进行数据库操作，提高项目开发的效率和质量。
PHP&MySQL范例精解_创建、修改、重用1.pdf
02-29
根据给定的信息，“PHP&MySQL范例精解_创建、修改、重用1.pdf”这份文档主要关注于PHP与MySQL数据库结合使用的实例讲解。虽然提供的具体内容部分并未包含实际的章节内容，但从标题和描述可以推测出，这本书籍将侧重...
MySQL从入门到精通PPT大全（13份）.rar
03-10
存储过程（Stored Procedures）和函数（Functions）可以帮助编写可重用的代码，提高效率并减少网络流量。索引（Indexes）的使用可以显著提高查询速度，但需要合理设计以避免负面影响。视图（Views）可以提供虚拟表，...
MySQL实现rownum
weixin_45873444的博客
08-24 6306
MySQL实现rownum CREATE TABLE T_TEST_AREA( ID INT, PID INT, NAME VARCHAR(20), DELETED INT, CHECKED INT ); DELETE FROM T_TEST_AREA WHERE 1=1; INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(1, -1, '长沙市', 0, 1); INSERT INTO T_TEST_AREA(ID,
Mysql的Rownum实现
songshuguowang的专栏
12-27 1027
今天被mysql的group by 再度坑了一把 测试环境mysql版本5.1，生产环境mysql版本5.7 于是测试好的功能，到生产垮了。。。 因为group by 的内部算法大约变了，导致同样的sql不同环境执行结果不同（不要问我为什么测试版本数据库版本与生产不一致，想想就闹心） 原SQL就不贴了，简化一下如下 select s.column1, s.column2, s.colu...
Mysql rownum 实现
qq_22075913的博客
07-28 1万+
Mysql rownum 实现
MySQL查询获取行号rownum
热门推荐
程序员蒋老湿的博客
09-27 2万+
MySQL中可以使用变量产生行号，下面是2个简单例子：使用工具：MySQL Workbench说明：表heyf_10中字段，empid(员工工号)、deptid(部门编号)、salary(薪资)；rownum是自定义变量，表示行号。对员工按照部门进行分组，分组后对相同部门的员工按照工资高低排序，并显示排序序号。rownum：自定义变量，表示行号；pdept：临时存放员工所属部门号；rank：部门...
mySQL RowNum用法
YIPCHIKWONG的博客
12-07 3771
select ROW_NUMBER() OVER(ORDER BY cpbzcb_ZRZXID ASC) AS 行号,//按部门代码排序再按行号排序 cpbzcb_ZRZXID AS 部门代码, cpbzcb_CPID as 物料编码, cpbzcb_BZCB as 标准成本, CPBZCB_WHR as 维护人, CPBZCB_WHSJ as 维护时间  from DBERP2.JS
mysql执行计划重用_Sql Server 编译、重编译与执行计划重用原理(转)
weixin_29342901的博客
02-08 168
对不同的指令调用方法， SQL Server 做执行计划缓存和重用机制也有所不同。下面介绍最常见的几种：1. Adhoc 语句一组包含 Select、Insert、Update、Delete 的批处理指令。对这样的指令，只有前后完全一直，包括字母的大小写、空格、回车换行都一致， SQL Server 才认为是两条一样的语句，才能够重用执行计划。所以这个要求还是挺高的。2. 用 Exec() 的方式...
mysql 空值和null的判断
lxf0613050210的博客
07-18 3386
一：判断空值 SELECT * FROM `users` where pwd=''; 结果： SELECT * FROM `users` where pwd !=''; 结果： 二：判断null值 SELECT * FROM `users` where pwd is NOT NULL; 结果： SELECT * FROM `user
MySQL开发技巧 - 存储过程
最新发布
10-08
下面是一些MySQL存储过程的开发技巧： 1. 参数传递：存储过程可以接受参数，在调用存储过程时传入参数。通过使用参数，可以使存储过程更加灵活和通用。 2. 错误处理：在存储过程中，可以使用条件语句和异常处理来...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值