1:虚拟表结构
id 要求完工时间 完成状态
1 2012-05-04 1
2 2012-05-04 1
3 2012-05-03 0
4 2012-05-03 1
5 2012-05-04 0
6 2012-05-03 02:需求
按 要求完工时间 查出 当前完工时间每个完成状态的count
table结构 完工时间 完成状态(0) 完成状态(1)
2012-05-04 1条 2条
2012-05-03 2条 1条在线等待 求大神赐教
id 要求完工时间 完成状态
1 2012-05-04 1
2 2012-05-04 1
3 2012-05-03 0
4 2012-05-03 1
5 2012-05-04 0
6 2012-05-03 02:需求
按 要求完工时间 查出 当前完工时间每个完成状态的count
table结构 完工时间 完成状态(0) 完成状态(1)
2012-05-04 1条 2条
2012-05-03 2条 1条在线等待 求大神赐教
SELECT 1 ID, '2012-05-04' ttime, 1 num FROM dual UNION ALL
SELECT 2, '2012-05-04', 1 FROM dual UNION ALL
SELECT 3, '2012-05-03', 0 FROM dual UNION ALL
SELECT 4, '2012-05-03', 1 FROM dual UNION ALL
SELECT 5, '2012-05-04', 0 FROM dual UNION ALL
SELECT 6, '2012-05-03', 0 FROM dual )
SELECT ttime,sum(decode(num,0,1)) sta1, sum(decode(num,1,1)) sta2 FROM t GROUP BY ttime;
(select date,sum(finishStatus) as finishStatus1 from tb where finishStatus=1 group by date)tb1
left join
(select date,sum(finishStatus) finishStatus0 from tb where finishStatus=0 group by date)tb0
on tb1.date=tb2.date
create table TestTbl as
select 1 as id,'2012-05-04' as sDate,1 as status from dual
union select 2,'2012-05-04',1 from dual
union select 3,'2012-05-03',0 from dual
union select 4,'2012-05-03',1 from dual
union select 5,'2012-05-04',0 from dual
union select 6,'2012-05-03',0 from dual;
select sDate ,
sum(case when status = 0 then 1 else 0 end) as status0,
sum(case when status = 1 then 1 else 0 end) as status1
from TestTbl
group by sDate