数据(1千万左右的数据 )
id userid inputDate infoStatus
1 1 2014-07-11 00:00:01 20013
2 1 2014-07-11 00:00:02 0
3 2 2014-07-12 00:00:03 20013
4 2 2014-07-12 00:00:04 20013
5 2 2014-07-13 00:00:05 20013
6 2 2014-07-13 00:00:06 0
7 2 2014-07-14 00:00:07 20013
8 2 2014-07-14 00:00:08 20014
9 2 2014-07-15 00:00:09 20013
10 2 2014-07-15 00:00:10 0
11 2 2014-07-15 00:00:11 0结果
2 1 2014-07-11 00:00:02 0
4 2 2014-07-12 00:00:04 20013
6 2 2014-07-13 00:00:06 0
8 2 2014-07-14 00:00:08 20014
10 2 2014-07-15 00:00:10 0
11 2 2014-07-15 00:00:11 0这样sql语句怎么写
注释:筛选出用户当天infoStatus等于0的,如果该用户当天infoStatus没有等于0则是该用户当天时间最大的记录
id userid inputDate infoStatus
1 1 2014-07-11 00:00:01 20013
2 1 2014-07-11 00:00:02 0
3 2 2014-07-12 00:00:03 20013
4 2 2014-07-12 00:00:04 20013
5 2 2014-07-13 00:00:05 20013
6 2 2014-07-13 00:00:06 0
7 2 2014-07-14 00:00:07 20013
8 2 2014-07-14 00:00:08 20014
9 2 2014-07-15 00:00:09 20013
10 2 2014-07-15 00:00:10 0
11 2 2014-07-15 00:00:11 0结果
2 1 2014-07-11 00:00:02 0
4 2 2014-07-12 00:00:04 20013
6 2 2014-07-13 00:00:06 0
8 2 2014-07-14 00:00:08 20014
10 2 2014-07-15 00:00:10 0
11 2 2014-07-15 00:00:11 0这样sql语句怎么写
注释:筛选出用户当天infoStatus等于0的,如果该用户当天infoStatus没有等于0则是该用户当天时间最大的记录
解决方案 »
- 外键问题求解决!
- SQL 混杂排序 求解中....
- asp与数据库Mysql连接问题
- 第一次接触MYSQL。请问,MYSQL需要安装吗?是不是与SQLSERVER一样,也需要运行一个服务?
- mysql如何同步主服务器和从服务器。谢谢。
- mysql的时间间隔问题?
- 请教大家一个很常见的select
- “delete from log;” ,不能执行 为啥?
- mysql 使用事件调度,将数据导出几个文件,如何实现?
- ubuntu系统,启动mysql的时候,出现start: Job failed to start,日志如下,这是什么问题?日志看不太明白
- mysql一条查询语句
- 关于MySQL的索引问题
from 数据 t
where infoStatus=0
or not exists (select 1 from 数据 inputDate>t.inputDate and userid=t.userid and date(inputDate)=date(t.inputDate))
我把你的改成:
select * from t2 t where infoStatus=0 or not exists (select * from t1 where inputDate>t.inputDate and userid=t.userid and date(inputDate)=date(t.inputDate));
只能过滤出0的,非0的取不出来
mysql> select * from t2;
+----+--------+---------------------+------------+
| id | userid | inputDate | infoStatus |
+----+--------+---------------------+------------+
| 1 | 1 | 2014-07-11 00:00:00 | 20013 |
| 2 | 1 | 2014-07-11 00:00:00 | 0 |
| 3 | 2 | 2014-07-12 00:00:11 | 20015 |
| 4 | 2 | 2014-07-12 00:00:22 | 20013 |
| 5 | 2 | 2014-07-14 00:00:00 | 0 |
| 6 | 2 | 2014-07-15 00:00:00 | 20013 |
| 7 | 2 | 2014-07-15 00:00:00 | 0 |
| 8 | 2 | 2014-07-16 00:00:00 | 0 |
| 9 | 2 | 2014-07-13 00:00:00 | 20013 |
| 10 | 2 | 2014-07-13 00:00:00 | 0 |
| 11 | 2 | 2014-07-14 00:00:00 | 0 |
+----+--------+---------------------+------------+查询sql:select id,userid,date(inputDate) as date,max(infoStatus) as m,min(infoStatus) as i,if(min(infoStatus)=0,0,max(infoStatus)) as max from t2 where infoStatus = 0 or infoStatus <= 99999 group by date;回执结果:
+----+--------+------------+-------+-------+-------+
| id | userid | date | m | i | max |
+----+--------+------------+-------+-------+-------+
| 1 | 1 | 2014-07-11 | 20013 | 0 | 0 |
| 3 | 2 | 2014-07-12 | 20015 | 20013 | 20015 |
| 9 | 2 | 2014-07-13 | 20013 | 0 | 0 |
| 5 | 2 | 2014-07-14 | 0 | 0 | 0 |
| 6 | 2 | 2014-07-15 | 20013 | 0 | 0 |
| 8 | 2 | 2014-07-16 | 0 | 0 | 0 |
+----+--------+------------+-------+-------+-------+我的思路是按照时间分组,并同时查询最小,跟最大的,如果最小的为0,那max字段就为0,否则就取最大的那个