求一条MYSQL 话语 - IDC云服务

求一条MYSQL 语句

SQL code


  
字段如下：
id  system_Id      log_date
1    1021     2012-06-27 15:21:29
2    1021     2012-07-01 12:21:30
3    1021     2012-07-02 17:21:31

4    12       2011-06-27 15:21:30
5    12       2012-06-02 13:29:11
6    12       2012-07-02 13:29:11
7    12       2012-07-02 13:29:11

8   218211    2012-07-02 13:29:11
9   218211    2012-07-02 13:29:12
10  218211    2012-07-02 13:29:13
11  218211    2012-07-02 13:29:14
12  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      3
12         1     2      4
218211     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) c
where a.system_Id=b.system_Id and a.system_Id=c.system_Id

相关文章：

你感兴趣的文章：

标签云：