MySQL分組排序取前N條記錄以及產生自動數字序列--group by 後 limit 外加 rownumber,--grouprownumber
同事提了一個需求,要求按照某列分組,然後將各組的前幾條抽取出來。
表結構
CREATE TABLE `total_freq_ctrl` ( `time` int(10) unsigned NOT NULL, `machine` char(64) NOT NULL, `module` char(32) NOT NULL, `total_flow` int(10) unsigned NOT NULL, `deny_flow` int(10) unsigned NOT NULL, PRIMARY KEY (`module`,`machine`,`time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
tudou@b2c.xiaomi.com
原sql
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
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; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+--------+---------------+------------+--------+| module | machine | time | rownum |+--------+---------------+------------+--------+| all | 10.201.20.181 | 1409640060 | 1 || all | 10.201.20.181 | 1409640000 | 2 || all | 10.201.20.181 | 1409639940 | 3 || all | 10.201.20.181 | 1409639880 | 4 || all | 10.201.20.97 | 1409640060 | 5 || all | 10.201.20.97 | 1409640000 | 6 || all | 10.201.20.97 | 1409639940 | 7 || all | 10.201.20.97 | 1409639880 | 8 || all | 10.201.20.98 | 1409640060 | 9 || all | 10.201.20.98 | 1409640000 | 10 |+--------+---------------+------------+--------+
rownumber已經出來了,再加一個@mid來進行分組
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;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+--------+---------------+------------+--------+---------------+| module | machine | time | rownum | @mid:=machine |+--------+---------------+------------+--------+---------------+| all | 10.201.20.181 | 1409640180 | 1 | 10.201.20.181 || all | 10.201.20.181 | 1409640120 | 2 | 10.201.20.181 || all | 10.201.20.181 | 1409640060 | 3 | 10.201.20.181 || all | 10.201.20.181 | 1409640000 | 4 | 10.201.20.181 || all | 10.201.20.181 | 1409639940 | 5 | 10.201.20.181 || all | 10.201.20.181 | 1409639880 | 6 | 10.201.20.181 || all | 10.201.20.97 | 1409640180 | 1 | 10.201.20.97 || all | 10.201.20.97 | 1409640120 | 2 | 10.201.20.97 || all | 10.201.20.97 | 1409640060 | 3 | 10.201.20.97 || all | 10.201.20.97 | 1409640000 | 4 | 10.201.20.97 || all | 10.201.20.97 | 1409639940 | 5 | 10.201.20.97 || all | 10.201.20.97 | 1409639880 | 6 | 10.201.20.97 || all | 10.201.20.98 | 1409640180 | 1 | 10.201.20.98 || all | 10.201.20.98 | 1409640120 | 2 | 10.201.20.98 || all | 10.201.20.98 | 1409640060 | 3 | 10.201.20.98 || all | 10.201.20.98 | 1409640000 | 4 | 10.201.20.98 || all | 10.201.20.98 | 1409639940 | 5 | 10.201.20.98 || all | 10.201.20.98 | 1409639880 | 6 | 10.201.20.98 |+--------+---------------+------------+--------+---------------+
好了,再外面加一層inner join 再對 rownumber 做限制 就可以拿到目標資料了。
tudou@b2c.xiaomi.com
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;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+------------+---------------+--------+------------+-----------+--------+| time | machine | module | total_flow | deny_flow | rownum |+------------+---------------+--------+------------+-----------+--------+| 1409640360 | 10.201.20.181 | all | 53937 | 6058 | 1 || 1409640300 | 10.201.20.181 | all | 52588 | 5701 | 2 || 1409640240 | 10.201.20.181 | all | 54254 | 5608 | 3 || 1409640180 | 10.201.20.181 | all | 54684 | 5811 | 4 || 1409640360 | 10.201.20.97 | all | 50679 | 5307 | 1 || 1409640300 | 10.201.20.97 | all | 50472 | 5239 | 2 || 1409640240 | 10.201.20.97 | all | 51586 | 5509 | 3 || 1409640180 | 10.201.20.97 | all | 50794 | 5378 | 4 || 1409640360 | 10.201.20.98 | all | 84747 | 5652 | 1 || 1409640300 | 10.201.20.98 | all | 84506 | 5696 | 2 || 1409640240 | 10.201.20.98 | all | 84982 | 5513 | 3 || 1409640180 | 10.201.20.98 | all | 83997 | 5623 | 4 |+------------+---------------+--------+------------+-----------+--------+
有同學推薦了這個連結:http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
mysql分組排序後取出幾條記錄,
# mysql不支援其它複雜資料庫的類似 rank() over 的排名和統計查詢
# 只能通過變通的子查詢和邏輯計算方式來實現,對於中小資料量可以考慮
-- rank 排名實現
select inline_rownum, aa, cc, amt, orderid FROM
(
select
# logic_cal 只是實現計數器計算的,每次逐條查詢時會對比當前 cc 與 @last_cc 是否相同,如果不同則把當前該列值賦於 @last_cc 並重設計數器 @num := 1,否則計數器自加 @num := @num + 1
(case when cc <> @last_cc then concat(@last_cc := cc, @num := 1 ) else concat(@last_cc, @num := @num + 1) end ) logic_cal
, @num as inline_rownum
, aa, cc, amt, orderid
from tb_rank,
( select @last_cc := '') t, # 初始化 @last_cc 為 '', 如要檢查的列(基於計數器統計的列)是int型,則初始化為0; varchar型初始化為''
( select @num := 0 ) t2 # 初始化@num為0
order by cc, orderid asc # 排序的方式會影響@num的產生,因為logic_cal是逐行計算的
) t
where inline_rownum <= floor(amt*0.8) #限制條數,取常量值或其他
order by cc,orderid asc
;
mysql 多表 查詢 之後 group by 分組 order by 不可以按照日期排序
你這個Sql語句本身都有問題,按分組的話,你顯示的欄位都要放在分組裡;
sql語句正確後,按時間降序排列是正確的