求一条MYSQL 话语

求一条MYSQL 语句

SQL code

字段如下：id  system_Id      log_date1    1021     2012-06-27 15:21:292    1021     2012-07-01 12:21:303    1021     2012-07-02 17:21:314    12       2011-06-27 15:21:305    12       2012-06-02 13:29:116    12       2012-07-02 13:29:117    12       2012-07-02 13:29:118   218211    2012-07-02 13:29:119   218211    2012-07-02 13:29:1210  218211    2012-07-02 13:29:1311  218211    2012-07-02 13:29:1412  218211    2012-07-02 13:29:15查询条件为 system_id  当前时间system_id 为1021 的记录当天、当月、当季度分别有多个行。system_id 为12的记录当天、当月、当季度分别有多个行。system_id 为218211的记录当天、当月、当季度分别有多个行。显示结果为：根据当前时间(2012-06-27)判断  log_date时间system_id days months quarters 1021       1     1      312         1     2      4218211     0     0      5 

[解决办法]
select system_id,count(case when date(log_date)=date(now()) then 1 end),
count(case when monthname(log_date)=monthname(now()) then 1 end),
count(case when quarter(log_date)=quarter(now()) then 1 end)
from tb
group by system_id
[解决办法]

SQL code

select a.system_Id,d,m,q from (select system_Id,count(*) as d from tabl1 where log_date>curdate() group by system_Id) a,(select system_Id,count(*) as m from tabl1 where log_date>curdate() -interval day(curdate())+1  group by system_Id) b,(select system_Id,count(*) as q from tabl1 where quarter(log_date)=quarter(curdate()) group by system_Id) cwhere a.system_Id=b.system_Id and a.system_Id=c.system_Id 

 

查看更多 下一篇