Mysql将数据分组后取出时间最近的数据
select * from AccountMark as b where not exists(select 1 from AccountMark where AccountId= b.AccountId
and b.CreateTime<CreateTime )
谢谢这位大神的解答,这样查出来的数据是正确的而且避免了查询子句的问题。但是如果我要在这里面统计出每一个AccountId有多少条Mark(最后一张截图里面的Num)该怎么写呢
select *,(select count(distinct mark) from AccountMark where AccountId= b.AccountId)
from AccountMark as b where not exists(select 1 from AccountMark where AccountId= b.AccountId
and b.CreateTime<CreateTime )
下面有两种方法:假设你的表叫t_biz_sign ,'最新列'字段叫create_time ,需要分组的字段叫foreign_id
SELECT sign.*
FROM t_biz_sign sign
WHERE NOT exists(SELECT 1
FROM t_biz_sign
WHERE foreign_id = sign.foreign_id
AND sign.create_time < create_time)
ORDER BY create_time DESC;
SELECT *
FROM t_biz_sign sign
WHERE (SELECT count(*)
FROM t_biz_sign AS m
WHERE m.foreign_id = sign.foreign_id AND m.create_time >= sign.create_time) < 2;
参考下贴中的多种方法
http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集,及散分....
经常看到问题,如何取出每组的前N条记录。方便大家参考于是便把常见的几种解法列出于下。
问题:有表 如下,要求取出各班前两名(允许并列第二)
Table1
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
| 1 |AAAA | C1 | 67 |
| 2 |BBBB | C1 | 55 |
| 3 |CCCC | C1 | 67 |
| 4 |DDDD | C1 | 65 |
| 5 |EEEE | C1 | 95 |
| 6 |FFFF | C2 | 57 |
| 7 |GGGG | C2 | 87 |
| 8 |HHHH | C2 | 74 |
| 9 |IIII | C2 | 52 |
| 10 |JJJJ | C2 | 81 |
| 11 |KKKK | C2 | 67 |
| 12 |LLLL | C2 | 66 |
| 13 |MMMM | C2 | 63 |
| 14 |NNNN | C3 | 99 |
| 15 |OOOO | C3 | 50 |
| 16 |PPPP | C3 | 59 |
| 17 |QQQQ | C3 | 66 |
| 18 |RRRR | C3 | 76 |
| 19 |SSSS | C3 | 50 |
| 20 |TTTT | C3 | 50 |
| 21 |UUUU | C3 | 64 |
| 22 |VVVV | C3 | 74 |
+----+------+------+-----+
结果如下
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
| 5 |EEEE | C1 | 95 |
| 1 |AAAA | C1 | 67 |
| 3 |CCCC | C1 | 67 |
| 7 |GGGG | C2 | 87 |
| 10 |JJJJ | C2 | 81 |
| 14 |NNNN | C3 | 99 |
| 18 |RRRR | C3 | 76 |
+----+------+------+-----+
方法一:
select a.id,a.SName,a.ClsNo,a.Score
from Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Score
group by a.id,a.SName,a.ClsNo,a.Score
having count(b.id)<2
order by a.ClsNo,a.Score desc
方法二:
select *
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc
方法三:
select *
from Table1 a
where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)
order by a.ClsNo,a.Score desc
方法....
这里列出了多种SQL语句的实现方法,有些是MySQL特有的(Limit, 其它数据库可根据实际更改,比如Oracle的rownum,MS SQL SERVER 的 top,..),有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况,索引情况进行选择。
特例 N=1 ,即取最大的/最小的一条记录。
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
| 5 |EEEE | C1 | 95 |
| 7 |GGGG | C2 | 87 |
| 14 |NNNN | C3 | 99 |
+----+------+------+-----+
select *
from Table1 a
where not exists (select 1 from Table1 where ClsNo=a.ClsNo and Score>a.Score);
select a.*
from Table1 a inner join (select ClsNo, max(Score) as mScore from Table1 group by ClsNo) b
on a.ClsNo=b.ClsNo and a.Score=b.Score
select *
from (select * from Table1 order by Score desc) t
group by ClsNo
推荐阅读
-
Excel2013数据透视表中如何将行区域中的时间字段按周分组
-
MySql分组后随机获取每组一条数据的操作
-
mysql分组后合并显示一个字段的多条数据方式
-
3.mysql的中文问题,database级操作,表级操作,数据CRUD,分组操作,时间和日期,字符串相关函数,表的约束_MySQL
-
mybatis从mysql数据库中取出的时间有时区问题
-
Mysql 统计最近七天、一月内的数据并分组
-
mysql数据库权限更改后的生效时间_MySQL
-
Mysql将数据分组后取出时间最近的数据
-
3.mysql的中文问题,database级操作,表级操作,数据CRUD,分组操作,时间和日期,字符串相关函数,表的约束_MySQL
-
关于Mysql如何将数据分组后取出时间最近的数据详解