按天去重复数据

Question

数据（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则是该用户当天时间最大的记录

Answer 1

select *
from 数据 t
where infoStatus=0
or not exists (select 1 from 数据 inputDate>t.inputDate and userid=t.userid and date(inputDate)=date(t.inputDate))

Answer 2

好像不行吧；
我把你的改成：
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的取不出来

Answer 3

测试数据：
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，否则就取最大的那个