mytable中有id,name,insertTime(datetime),upRange(varchar(6)值为 -8.88 +3.33 -0.23之类)字段
求最近10日内,upRange字段为负数的且数目超过3的 记录。
1. 最近十日内
2. upRange<0,且 count(upRange)>3。
注:insertTime值为:2009-06-04 18:49:29,2009-06-04 18:49:30,2009-06-05 19:52:31
求最近10日内,upRange字段为负数的且数目超过3的 记录。
1. 最近十日内
2. upRange<0,且 count(upRange)>3。
注:insertTime值为:2009-06-04 18:49:29,2009-06-04 18:49:30,2009-06-05 19:52:31
from mytable
where insertTime>=DATE_SUB(CURDATE(),INTERVAL 10 DAY) -- 最近十日内
and upRange<0
group by id,name
having count(*)>3
INNER JOIN
(select id,name from TT
where insertTime>=DATE_ADD(date,INTERVAL 10 DAY ) and upRange<0
group by id,name
having count(*)>3 ) B
ON A.ID=B.ID AND A.NAME=B.NAME
INNER JOIN
(select id,name from TT
where insertTime>=DATE_ADD(curdate(),INTERVAL -10 DAY ) and upRange <0
group by id,name
having count(*)>3 ) B
ON A.ID=B.ID AND A.NAME=B.NAME
比如今日10号,应该是31-10日吗?
如果是
SELECT A.* FROM TT A
INNER JOIN
(select id,name from TT
where (insertTime between DATE_ADD(curdate(),INTERVAL -10 DAY ) and DATE_ADD(curdate(),INTERVAL -1 DAY ))and upRange <0
group by id,name
having count(*)>3 ) B
ON A.ID=B.ID AND A.NAME=B.NAME