mysql分组取每组前⼏条记录（排名）附groupby与
orderby的研究
--按某⼀字段分组取最⼤(⼩)值所在⾏的数据
复制代码代码如下:
/*
数据如下：
name val memo
a 2 a2(a的第⼆个值)
a 1 a1--a的第⼀个值
a 3 a3:a的第三个值
b 1 b1--b的第⼀个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插⼊数据：
复制代码代码如下:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第⼆个值)')
insert into tb values('a', 1, 'a1--a的第⼀个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第⼀个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--⼀、按name分组取val最⼤的值所在⾏的数据。
复制代码代码如下:
--⽅法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的第三个值
b 5 b5b5b5b5b5
*/
本⼈推荐使⽤1,3,4，结果显⽰1,3,4效率相同，2，5效率差些，不过我3,4效率相同毫⽆疑问，1就不⼀样了，想不搞了。
--⼆、按name分组取val最⼩的值所在⾏的数据。
复制代码代码如下:
--⽅法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的第⼀个值
b 1 b1--b的第⼀个值
*/
--三、按name分组取第⼀次出现的⾏所在的数据。
复制代码代码如下:
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的第⼆个值)
b 1 b1--b的第⼀个值
*/
-
-四、按name分组随机取⼀条数据。
复制代码代码如下:
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的第⼀个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最⼩的两个(N个)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.valselect 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的第⼀个值
a 2 a2(a的第⼆个值)
b 1 b1--b的第⼀个值
b 2 b2b2b2b2
*/
--六、按name分组取最⼤的两个(N个)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的第⼆个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七，假如整⾏数据有重复，所有的列都相同（例如下表中的第5,6两⾏数据完全相同）。
按name分组取最⼤的两个(N个)val
复制代码代码如下:
/*
数据如下：
name val memo
a 2 a2(a的第⼆个值)
a 1 a1--a的第⼀个值
a 1 a1--a的第⼀个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第⼀个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
附：mysql “group by ”与"order by"的研究
这两天让⼀个数据查询难了。主要是对group by 理解的不够深⼊。才出现这样的情况
这种需求，我想很多⼈都遇到过。下⾯是我模拟我的内容表
我现在需要取出每个分类中最新的内容
select * from test group by category_id order by `date`
结果如下
明显。这不是我想要的数据，原因是msyql已经的执⾏顺序是
引⽤
写的顺序：select ... order by..
执⾏顺序： select ...
所以在order by拿到的结果⾥已经是分组的完的最后结果。
由from到where的结果如下的内容。
到group by时就得到了根据category_id分出来的多个⼩组
到了select的时候，只从上⾯的每个组⾥取第⼀条信息结果会如下
即使order by也只是从上⾯的结果⾥进⾏排序。并不是每个分类的最新信息。
回到我的⽬的上－－分类中最新的信息
根据上⾯的分析，group by到select时只取到分组⾥的第⼀条信息。有两个解决⽅法
1，where+group by（对⼩组进⾏排序）
2，从form返回的数据下⼿脚（即⽤⼦查询）
对group by⾥的⼩组进⾏排序的函数我只查到group_concat()可以进⾏排序，但group_concat的作⽤是将⼩组⾥的字段⾥的值进⾏串联起来。
select group_concat(id order by `date` desc) from `test` group by category_id
groupby分组
再改进⼀下
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。