贴记录及正确结果出来看看 每天有多少个STATUS=0的NAME SELECT NAME,COUNT(*) FROM TT GROUP BY NAME WHERE STATUS=0
GG ,没有后面的条件啊“在这条记录的日期之前没有该NAME的记录” ,需要 group by left(from_unixtime(TIME),10)
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)猜一下你想要的结果。我的汉语水平一般。猜谜水平更差,所以猜错了也很正常。select name from a t1 where STATUS=0 and not exist (select 1 from a where name=t1.name and time<t1.time)
NAME TIME STATUSA 1224918001 0 B 1224918002 1 A 1224918003 1 B 1224918004 0 C 1224918005 0 D 1224918006 0 E 1224918007 0 -----这是 2008-10-26 的数据 A 1225004401 0 B 1225004402 1 A 1225004403 1 C 1225004405 0 D 1225004406 0 E 1225004407 0 F 1225004408 0 G 1225004409 0 H 1225004410 0 -----这是 2008-10-27 的数据 C 1225090805 0 D 1225090806 0 E 1225090807 0 F 1225090808 0 G 1225090809 0 H 1225090810 0 J 1225090809 1 I 1225090810 0 -----这是 2008-10-28 的数据 结果是 5 2008-10-26 (A,B,C,D,E) 3 2008-10-27 (F,G,H) 1 2008-10-28 (I)注意:括号里的是提示,不是结果。
select count(distinct(NAME)) as num,left(from_unixtime(TIME),10) as htime from stat t1 where STATUS=0 and not exist (select 1 from stat where NAME=t1.NAME and TIME<((t1.TIME/(24*3600))*24*3600) ) group by left(htime,10) order by htime ;大概意思是这样的,但是发现这个语句不对啊
下面这句应该可以实现,不用太过复杂SQL啊;自己写得辛苦,看得人更加辛苦。 select a.time,count(*) from a where a.status = 0 group by (a.TIME/(24*3600))*24*3600)
select time,count(*) from a t1 where STATUS=0 and not exist (select 1 from a where name=t1.name and time<t1.time) group by time
和我写的基本一样吧 select count(distinct(NAME)) as num,left(from_unixtime(TIME),10) as htime from stat t1 where STATUS=0 and not exist (select 1 from stat where NAME=t1.NAME and TIME <((t1.TIME/(24*3600))*24*3600) ) group by left(htime,10) order by htime ; mysql 说 and not exist (select 1 from stat where NAME=t1.NAME and TIME <((t1.TIME/(24*3600))*24*3600) ) 这有错误
假设不累计,只与前一天相比: 加入FF字段,内容为 2008-10-26 2008-10-27 2008-10-28SELECT A.FF,COUNT(*) FROM ( SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.FF=DATEADD('D',1,B.FF) AND A.NAME=B.NAME WHERE B.NAME IS NULL GROUP BY A.FF,A.NAME) GROUP BY A.FF
J STATUS=1 ,所以不算 。
更简单: SELECT A.FF, COUNT(*) FROM (SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.time>B.time AND A.NAME=B.NAME WHERE B.NAME IS NULL GROUP BY A.FF,A.NAME) GROUP BY A.FF;
SELECT A.FF, COUNT(*) FROM (SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.ff>B.ff AND A.NAME=B.NAME WHERE B.NAME IS NULL and a.STATUS=0 GROUP BY A.FF,A.NAME ) GROUP BY A.FF;
select time,count(*) from a t1 where STATUS=0 and not exists (select 1 from a where name=t1.name and time<t1.time) group by timeexist -> exists
我的意思是 group by left(from_unixtime(A.TIME),10) , 但是我把你的A.FF 替换为left(from_unixtime(A.TIME),10) 不对啊。
每天有多少个STATUS=0的NAME
SELECT NAME,COUNT(*) FROM TT GROUP BY NAME WHERE STATUS=0
GG ,没有后面的条件啊“在这条记录的日期之前没有该NAME的记录” ,需要 group by left(from_unixtime(TIME),10)
from a t1
where STATUS=0
and not exist (select 1 from a where name=t1.name and time<t1.time)
B 1224918002 1
A 1224918003 1
B 1224918004 0
C 1224918005 0
D 1224918006 0
E 1224918007 0 -----这是 2008-10-26 的数据
A 1225004401 0
B 1225004402 1
A 1225004403 1
C 1225004405 0
D 1225004406 0
E 1225004407 0
F 1225004408 0
G 1225004409 0
H 1225004410 0 -----这是 2008-10-27 的数据
C 1225090805 0
D 1225090806 0
E 1225090807 0
F 1225090808 0
G 1225090809 0
H 1225090810 0
J 1225090809 1
I 1225090810 0 -----这是 2008-10-28 的数据
结果是
5 2008-10-26 (A,B,C,D,E)
3 2008-10-27 (F,G,H)
1 2008-10-28 (I)注意:括号里的是提示,不是结果。
where STATUS=0
and not exist (select 1 from stat where NAME=t1.NAME and TIME<((t1.TIME/(24*3600))*24*3600) )
group by left(htime,10) order by htime ;大概意思是这样的,但是发现这个语句不对啊
select a.time,count(*) from a where a.status = 0 group by (a.TIME/(24*3600))*24*3600)
from a t1
where STATUS=0
and not exist (select 1 from a where name=t1.name and time<t1.time)
group by time
和我写的基本一样吧
select count(distinct(NAME)) as num,left(from_unixtime(TIME),10) as htime from stat t1
where STATUS=0
and not exist (select 1 from stat where NAME=t1.NAME and TIME <((t1.TIME/(24*3600))*24*3600) )
group by left(htime,10) order by htime ;
mysql 说 and not exist (select 1 from stat where NAME=t1.NAME and TIME <((t1.TIME/(24*3600))*24*3600) ) 这有错误
加入FF字段,内容为
2008-10-26
2008-10-27
2008-10-28SELECT A.FF,COUNT(*) FROM (
SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.FF=DATEADD('D',1,B.FF)
AND A.NAME=B.NAME WHERE B.NAME IS NULL GROUP BY A.FF,A.NAME) GROUP BY A.FF
J STATUS=1 ,所以不算 。
SELECT A.FF, COUNT(*)
FROM (SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.time>B.time
AND A.NAME=B.NAME WHERE B.NAME IS NULL GROUP BY A.FF,A.NAME)
GROUP BY A.FF;
FROM (SELECT A.FF,A.NAME FROM TTR A LEFT JOIN TTR B ON A.ff>B.ff
AND A.NAME=B.NAME WHERE B.NAME IS NULL and a.STATUS=0 GROUP BY A.FF,A.NAME )
GROUP BY A.FF;
from a t1
where STATUS=0
and not exists (select 1 from a where name=t1.name and time<t1.time)
group by timeexist -> exists
我的意思是 group by left(from_unixtime(A.TIME),10) , 但是我把你的A.FF 替换为left(from_unixtime(A.TIME),10) 不对啊。
2008-10-26
2008-10-27
2008-10-28
即根据5楼的日期输入FF的值,因为你的结果要求日期,根据TIME怎么
得出日期?