现在有一个表A
UNIT TIME TYPE
320281840000 2010-11-17 20:02:44 CallType060000
320281840000 2009-11-17 17:10:15 CallType060000
320281840000 2010-11-17 17:12:15 CallType070000
320281840000 2010-11-17 17:14:15 CallType070000
320281820000 2009-11-17 17:19:15 CallType020000从程序里传过来2个时间段,假设第一个时间段:2010-11-10到2010-11-19 第二个时间段:2009-11-10到2009-11-19
请问如何用SQL语句查询结果集如下(其中TIME1COUNT是指按照第一个时间段查询,TIME2COUNT是第二个时间段):
UNIT TYPE TIME1COUNT TIME2COUNT
320281840000 CallType060000 1 1
320281840000 CallType070000 2 0
320281820000 CallType020000 0 1
UNIT TIME TYPE
320281840000 2010-11-17 20:02:44 CallType060000
320281840000 2009-11-17 17:10:15 CallType060000
320281840000 2010-11-17 17:12:15 CallType070000
320281840000 2010-11-17 17:14:15 CallType070000
320281820000 2009-11-17 17:19:15 CallType020000从程序里传过来2个时间段,假设第一个时间段:2010-11-10到2010-11-19 第二个时间段:2009-11-10到2009-11-19
请问如何用SQL语句查询结果集如下(其中TIME1COUNT是指按照第一个时间段查询,TIME2COUNT是第二个时间段):
UNIT TYPE TIME1COUNT TIME2COUNT
320281840000 CallType060000 1 1
320281840000 CallType070000 2 0
320281820000 CallType020000 0 1
解决方案 »
- Oracle中的外连接
- 下面的过程为什么执行时会出现下列错误
- pro c中时间数据问题?
- 求一sql写法!怎么查询结果集的第几条到第几条数据啊
- Oracle联机日志REDO01.LOG不匹配,无法启动数据库问题?
- oracle10g的em出不来?高手进,高分伺候!100分不够,另开贴拜谢!
- 怎样取出刚刚插入的记录的id
- 代码测试求助
- 大侠们,帮我学ORACLE指个路!!!
- 一个过程执行时提示对象不存在了,可查看都好好的。
- 求一SQL语句.........................................标题要长
- 这语句怎么写?时间最新的 两条 总公司的记录,与任意其他子公司的时间最新的 N条记录
SELECT '320281840000' UNIT, To_Date('2010-11-17 20:02:44','yyyy-mm-dd hh24:mi:ss')TIME, 'CallType060000' TYPE FROM dual UNION ALL
SELECT '320281840000', To_Date('2009-11-17 17:10:15','yyyy-mm-dd hh24:mi:ss'), 'CallType060000' FROM dual UNION ALL
SELECT '320281840000', To_Date('2010-11-17 17:12:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' FROM dual UNION ALL
SELECT '320281840000', To_Date('2010-11-17 17:14:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' FROM dual UNION ALL
SELECT '320281820000', To_Date('2009-11-17 17:19:15','yyyy-mm-dd hh24:mi:ss'), 'CallType020000' FROM dual
)
SELECT unit,TYPE,
Sum(CASE WHEN time BETWEEN To_Date('2010-11-10','yyyy-mm-dd') AND To_Date('2010-11-19','yyyy-mm-dd') --第一个时间段
THEN 1
ELSE 0
END) TIME1COUNT,
Sum(CASE WHEN time BETWEEN To_Date('2009-11-10','yyyy-mm-dd') AND To_Date('2009-11-19','yyyy-mm-dd') --第二个时间段
THEN 1
ELSE 0
END)TIME2COUNT
FROM a
GROUP BY unit,type
ORDER BY unit,type--结果:
UNIT TYPE TIME1COUNT TIME2COUNT
-------------------------------------------------
320281820000 CallType020000 0 1
320281840000 CallType060000 1 1
320281840000 CallType070000 2 0
with tabA as (
select '320281840000' UNIT ,timestamp '2010-11-17 20:02:44' atime ,'CallType060000' type from dual union all
select '320281840000' UNIT ,timestamp '2009-11-17 17:10:15' atime ,'CallType060000' type from dual union all
select '320281840000' UNIT ,timestamp '2010-11-17 17:12:15' atime ,'CallType070000' type from dual union all
select '320281840000' UNIT ,timestamp '2010-11-17 17:14:15' atime ,'CallType070000' type from dual union all
select '320281820000' UNIT ,timestamp '2009-11-17 17:19:15' atime ,'CallType020000' type from dual
)
select unit,
type,
sum(case when atime between starttime1 and endtime1 then 1 else 0 end) TIME1COUNT, ------第一个时间段
sum(case when atime between starttime1 and endtime2 then 1 else 0 end) TIME2COUNT ------第二个时间短
from tabA
group by unit, type
SQL> with tb as(
2 select '320281840000' UNIT, To_Date('2010-11-17 20:02:44','yyyy-mm-dd hh24:mi:ss') TIME, 'CallType060000' TYPE from dual union all
3 select '320281840000', To_Date('2009-11-17 17:10:15','yyyy-mm-dd hh24:mi:ss'), 'CallType060000' from dual union all
4 select '320281840000', To_Date('2010-11-17 17:12:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' from dual union all
5 select '320281840000', To_Date('2010-11-17 17:14:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' from dual union all
6 select '320281820000', To_Date('2009-11-17 17:19:15','yyyy-mm-dd hh24:mi:ss'), 'CallType020000' from dual)
7 select nvl(a.UNIT,b.UNIT) "UNIT",nvl(a.type,b.type) "type",
8 nvl(TIME1COUNT,0) "TIME1COUNT",nvl(TIME2COUNT,0) "TIME2COUNT"
9 from (select UNIT,type,count(*) TIME1COUNT
10 from tb
11 where time between to_date('2010-11-10','yyyy-mm-dd') and to_date('2010-11-19','yyyy-mm-dd')
12 group by UNIT,type) a full outer join
13 (select UNIT,type,count(*) TIME2COUNT
14 from tb
15 where time between to_date('2009-11-10','yyyy-mm-dd') and to_date('2009-11-19','yyyy-mm-dd')
16 group by UNIT,type) b on a.UNIT=b.UNIT and a.type=b.type
17 /
UNIT type TIME1COUNT TIME2COUNT
------------ -------------- ---------- ----------
320281840000 CallType060000 1 1
320281840000 CallType070000 2 0
320281820000 CallType020000 0 1
2 select '320281840000' UNIT, To_Date('2010-11-17 20:02:44','yyyy-mm-dd hh24:mi:ss') TIME, 'CallType060000' TYPE from dual union all
3 select '320281840000', To_Date('2009-11-17 17:10:15','yyyy-mm-dd hh24:mi:ss'), 'CallType060000' from dual union all
4 select '320281840000', To_Date('2010-11-17 17:12:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' from dual union all
5 select '320281840000', To_Date('2010-11-17 17:14:15','yyyy-mm-dd hh24:mi:ss'), 'CallType070000' from dual union all
6 select '320281820000', To_Date('2009-11-17 17:19:15','yyyy-mm-dd hh24:mi:ss'), 'CallType020000' from dual)
7 select UNIT,type,
8 sum(case
9 when trunc(TIME) between to_date('2010-11-10','yyyy-mm-dd') and to_date('2010-11-19','yyyy-mm-dd') then 1 else 0 end) "TIME1COUNT",
10 sum(case
11 when trunc(TIME) between to_date('2009-11-10','yyyy-mm-dd') and to_date('2009-11-19','yyyy-mm-dd') then 1 else 0 end) "TIME2COUNT"
12 from tb
13 group by UNIT,type
14 /
UNIT TYPE TIME1COUNT TIME2COUNT
------------ -------------- ---------- ----------
320281840000 CallType070000 2 0
320281820000 CallType020000 0 1
320281840000 CallType060000 1 1