mysql分組取每組前幾條記錄(排名) 附group by與order by的研究

--按某一字段分組取最大(小)值所在行的數(shù)據(jù)/*

數(shù)據(jù)如下：
name?val?memo
a????2???a2(a的第二個(gè)值)
a????1???a1--a的第一個(gè)值
a????3???a3:a的第三個(gè)值
b????1???b1--b的第一個(gè)值
b????3???b3:b的第三個(gè)值
b????2???b2b2b2b2
b????4???b4b4
b????5???b5b5b5b5b5

*/??

?

--創(chuàng)建表并插入數(shù)據(jù)：

?

create?table?tb(name?varchar(10),val?int,memo?varchar(20))
insert?into?tb?values('a',????2,???'a2(a的第二個(gè)值)')
insert?into?tb?values('a',????1,???'a1--a的第一個(gè)值')
insert?into?tb?values('a',????3,???'a3:a的第三個(gè)值')
insert?into?tb?values('b',????1,???'b1--b的第一個(gè)值')
insert?into?tb?values('b',????3,???'b3:b的第三個(gè)值')
insert?into?tb?values('b',????2,???'b2b2b2b2')
insert?into?tb?values('b',????4,???'b4b4')
insert?into?tb?values('b',????5,???'b5b5b5b5b5')

go?

?--一、按name分組取val最大的值所在行的數(shù)據(jù)。

?--方法1： select?a.*?from?tb?a?where?val?=?(select?max(val)?from?tb?where?name?=?a.name)?order?by?a.name
--方法2：
select?a.*?from?tb?a?where?not?exists(select?1?from?tb?where?name?=?a.name?and?val?>?a.val)
--方法3：
select?a.*?from?tb?a,(select?name,max(val)?val?from?tb?group?by?name)?b?where?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name
--方法4：
select?a.*?from?tb?a?inner?join?(select?name?,?max(val)?val?from?tb?group?by?name)?b?on?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name
--方法5
select?a.*?from?tb?a?where?1?>?(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?)?order?by?a.name
/*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????3???????????a3:a的第三個(gè)值
b??????????5???????????b5b5b5b5b5

*/?

?本人推薦使用1,3,4,結(jié)果顯示1,3,4效率相同，2，5效率差些，不過我3,4效率相同毫無疑問，1就不一樣了，想不搞了。

--二、按name分組取val最小的值所在行的數(shù)據(jù)。

--方法1： select?a.*?from?tb?a?where?val?=?(select?min(val)?from?tb?where?name?=?a.name)?order?by?a.name
--方法2：
select?a.*?from?tb?a?where?not?exists(select?1?from?tb?where?name?=?a.name?and?val?<?a.val)
--方法3：
select?a.*?from?tb?a,(select?name,min(val)?val?from?tb?group?by?name)?b?where?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name
--方法4：
select?a.*?from?tb?a?inner?join?(select?name?,?min(val)?val?from?tb?group?by?name)?b?on?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name
--方法5
select?a.*?from?tb?a?where?1?>?(select?count(*)?from?tb?where?name?=?a.name?and?val?<?a.val)?order?by?a.name
/*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????1???????????a1--a的第一個(gè)值
b??????????1???????????b1--b的第一個(gè)值

*/?

?--三、按name分組取第一次出現(xiàn)的行所在的數(shù)據(jù)。

select?a.*?from?tb?a?where?val?=?(select?top?1?val?from?tb?where?name?=?a.name)?order?by?a.name
/*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????2???????????a2(a的第二個(gè)值)
b??????????1???????????b1--b的第一個(gè)值

*/??

?

--四、按name分組隨機(jī)取一條數(shù)據(jù)。select?a.*?from?tb?a?where?val?=?(select?top?1?val?from?tb?where?name?=?a.name?order?by?newid())?order?by?a.name /*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????1???????????a1--a的第一個(gè)值
b??????????5???????????b5b5b5b5b5

*/?

?--五、按name分組取最小的兩個(gè)(N個(gè))val

select?a.*?from?tb?a?where?2?>?(select?count(*)?from?tb?where?name?=?a.name?and?val?<?a.val?)?order?by?a.name,a.val select?a.*?from?tb?a?where?val?in?(select?top?2?val?from?tb?where?name=a.name?order?by?val)?order?by?a.name,a.val
select?a.*?from?tb?a?where?exists?(select?count(*)?from?tb?where?name?=?a.name?and?val?<?a.val?having?Count(*)?<?2)?order?by?a.name
/*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????1???????????a1--a的第一個(gè)值
a??????????2???????????a2(a的第二個(gè)值)
b??????????1???????????b1--b的第一個(gè)值
b??????????2???????????b2b2b2b2

*/?

?--六、按name分組取最大的兩個(gè)(N個(gè))val

select?a.*?from?tb?a?where?2?>?(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?)?order?by?a.name,a.val
select?a.*?from?tb?a?where?val?in?(select?top?2?val?from?tb?where?name=a.name?order?by?val?desc)?order?by?a.name,a.val
select?a.*?from?tb?a?where?exists?(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?having?Count(*)?<?2)?order?by?a.name
/*
name???????val?????????memo????????????????
----------?-----------?--------------------
a??????????2???????????a2(a的第二個(gè)值)
a??????????3???????????a3:a的第三個(gè)值
b??????????4???????????b4b4
b??????????5???????????b5b5b5b5b5

*/??

?

--七，假如整行數(shù)據(jù)有重復(fù)，所有的列都相同（例如下表中的第5,6兩行數(shù)據(jù)完全相同）。 ??????? 按name分組取最大的兩個(gè)(N個(gè))val?1??/*
?2?數(shù)據(jù)如下：
?3?name?val?memo
?4?a????2???a2(a的第二個(gè)值)
?5?a????1???a1--a的第一個(gè)值
?6?a????1???a1--a的第一個(gè)值
?7?a????3???a3:a的第三個(gè)值
?8?a????3???a3:a的第三個(gè)值
?9?b????1???b1--b的第一個(gè)值
10?b????3???b3:b的第三個(gè)值
11?b????2???b2b2b2b2
12?b????4???b4b4
13?b????5???b5b5b5b5b5
14?
15?*/?
16

?

?

?附：mysql “group by ”與"order by"的研究－－分類中最新的內(nèi)容

?這兩天讓一個(gè)數(shù)據(jù)查詢難了。主要是對group by 理解的不夠深入。才出現(xiàn)這樣的情況

這種需求，我想很多人都遇到過。下面是我模擬我的內(nèi)容表

我現(xiàn)在需要取出每個(gè)分類中最新的內(nèi)容
select * from test group by category_id order by `date`
結(jié)果如下

明顯。這不是我想要的數(shù)據(jù)，原因是msyql已經(jīng)的執(zhí)行順序是

引用
寫的順序：select ... from... where.... group by... having... order by..
執(zhí)行順序：from... where...group by... having.... select ... order by...
所以在order by拿到的結(jié)果里已經(jīng)是分組的完的最后結(jié)果。
由from到where的結(jié)果如下的內(nèi)容。

到group by時(shí)就得到了根據(jù)category_id分出來的多個(gè)小組


到了select的時(shí)候，只從上面的每個(gè)組里取第一條信息結(jié)果會(huì)如下

即使order by也只是從上面的結(jié)果里進(jìn)行排序。并不是每個(gè)分類的最新信息。
回到我的目的上 －－分類中最新的信息
根據(jù)上面的分析，group by到select時(shí)只取到分組里的第一條信息。有兩個(gè)解決方法
1，where+group by（對小組進(jìn)行排序）
2，從form返回的數(shù)據(jù)下手腳（即用子查詢）

由where+group by的解決方法
對group by里的小組進(jìn)行排序的函數(shù)我只查到group_concat()可以進(jìn)行排序，但group_concat的作用是將小組里的字段里的值進(jìn)行串聯(lián)起來。
select group_concat(id order by `date` desc) from `test` group by category_id

再改進(jìn)一下
select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc


子查詢解決方案
select * from (select * from `test` order by `date` desc) `temp`??group by category_id order by `date` desc

?