现在,我有一个多选子查询,它根据计算的一天中的小时数来获取数据.我现在要做的是在该查询中引入另一个表,并根据id和原始表中的日期时间进行计数.
我现在拥有的是：
select
(
select count(a_date)
from t1
where d_date
between '2013-01-07 00:00:00' and '2013-01-07 00:59:59'
) AS '00:00 to 00:59',(
select count(a_date)
from t1
where d_date
between '2013-01-07 01:00:00' and '2013-01-07 01:59:59'
) AS '01:00 to 01:59'
等等,直到一天结束.
我有另一个查询,它根据id和datetime给我计数,但只有两列,一列显示c_name,另一列显示小时计数.
防爆.
select t2.c_name,count(t1.a_date)
from t2 join t1
on t2.t1_key = t1.t2_key
where t1.d_date
between '2013-01-07 00:00:00' and '2013-01-07 00:59:59'
group by t2.c_id
基本上,我想将这两个查询组合成一个可以显示c_name和一天中所有小时的查询.
有什么建议？
最佳答案
我会研究使用CASE语句.
尝试这样的事情(添加额外的23列)：
select c_name,SUM(case when HOUR(d_date) = 0 then 1 else 0 end) '00:00 to 00:59',SUM(case when HOUR(d_date) = 1 then 1 else 0 end) '01:00 to 01:59'
from t2
join t1 on t2.t1_key = t1.t2_key
group by c_name
这是SQL Fiddle.
您只需要为d_date添加WHERE标准 – 例如：
where d_date between '2013-01-07 00:00:00' and '2013-01-07 23:59:59'
要么
where Date(d_date) = '2013-01-07'
祝好运！