现有如下结构A表
ID 场所ID 部门ID 日期
1 29 1 2008-1-2
2 29 1 2009-2-22
3 29 1 2008-1-10
4 29 2 2008-4-2
5 28 2 2008-3-2
6 28 2 2009-3-6
7 28 2 2009-3-11
8 28 3 2008-3-1
9 21 3 2008-5-3
10 21 3 2009-4-3想通过查询得到如下统计信息B表
月份 场所ID 部门ID 总计
2008-1 29 1 2
2009-2 29 1 1
2008-4 29 2 1
2008-3 28 2 1
2009-3 28 2 2
2008-3 28 3 1
2008-5 21 3 1
2009-4 21 3 1其中只要 场所ID,月份,部门ID 这三个字段只要有一个不一致 就作为一条count进行计数
ID 场所ID 部门ID 日期
1 29 1 2008-1-2
2 29 1 2009-2-22
3 29 1 2008-1-10
4 29 2 2008-4-2
5 28 2 2008-3-2
6 28 2 2009-3-6
7 28 2 2009-3-11
8 28 3 2008-3-1
9 21 3 2008-5-3
10 21 3 2009-4-3想通过查询得到如下统计信息B表
月份 场所ID 部门ID 总计
2008-1 29 1 2
2009-2 29 1 1
2008-4 29 2 1
2008-3 28 2 1
2009-3 28 2 2
2008-3 28 3 1
2008-5 21 3 1
2009-4 21 3 1其中只要 场所ID,月份,部门ID 这三个字段只要有一个不一致 就作为一条count进行计数
select to_char(日期,'yyyy-mm') 月份,场所ID,部门ID,count(1) 总计
from A
group by to_char(日期,'yyyy-mm') 月份,场所ID,部门ID
select 1 id,29 addressid,1 depoid,to_date('2008-1-2','yyyy-mm-dd') sdate from dual
union all
select 2 id,29 addressid,1 depoid,to_date('2009-2-22','yyyy-mm-dd') sdate from dual
union all
select 3 id,29 addressid,1 depoid,to_date('2008-1-10','yyyy-mm-dd') sdate from dual
union all
select 4 id,29 addressid,2 depoid,to_date('2008-4-2','yyyy-mm-dd') sdate from dual
union all
select 5 id,28 addressid,2 depoid,to_date('2008-3-2','yyyy-mm-dd') sdate from dual
union all
select 6 id,28 addressid,2 depoid,to_date('2009-3-6','yyyy-mm-dd') sdate from dual
union all
select 7 id,28 addressid,2 depoid,to_date('2009-3-11','yyyy-mm-dd') sdate from dual
union all
select 8 id,28 addressid,3 depoid,to_date('2008-3-1','yyyy-mm-dd') sdate from dual
union all
select 9 id,21 addressid,3 depoid,to_date('2008-5-3','yyyy-mm-dd') sdate from dual
union all
select 10 id,21 addressid,3 depoid,to_date('2009-4-3','yyyy-mm-dd') sdate from dual
)
select to_char(sdate,'yyyy-mm'),addressid,depoid,count(id) from temp
group by to_char(sdate,'yyyy-mm'),addressid,depoid;
SQL> with temp as(
2 select 1 id,29 addID,1 deptid,to_date('2008-1-2','yyyy-mm-dd') tdate from dual
3 union all
4 select 2 ,29 ,1 ,to_date('2009-2-22','yyyy-mm-dd') from dual
5 union all
6 select 3 ,29 ,1 ,to_date('2008-1-10','yyyy-mm-dd') from dual
7 union all
8 select 4 ,29 ,2 ,to_date('2008-4-2','yyyy-mm-dd') from dual
9 union all
10 select 5 ,28 ,2 ,to_date('2008-3-2','yyyy-mm-dd') from dual
11 union all
12 select 6 ,28 ,2 ,to_date('2009-3-6','yyyy-mm-dd') from dual
13 union all
14 select 7 ,28 ,2 ,to_date('2009-3-11','yyyy-mm-dd') from dual
15 union all
16 select 8 ,28 ,3 ,to_date('2008-3-1','yyyy-mm-dd') from dual
17 union all
18 select 9 ,21 ,3 ,to_date('2008-5-3','yyyy-mm-dd') from dual
19 union all
20 select 10 ,21 ,3 ,to_date('2009-4-3','yyyy-mm-dd') from dual
21 )
22 select to_char(trunc(tdate,'mm'),'yyyy-MM'),addID,deptid,count(*) from temp group by trunc(tdate,'mm'),addID,deptid
23 /TO_CHAR(TRUNC(TDATE,'MM'),'YYY ADDID DEPTID COUNT(*)
------------------------------ ---------- ---------- ----------
2008-03 28 3 1
2008-03 28 2 1
2009-04 21 3 1
2008-05 21 3 1
2008-04 29 2 1
2009-03 28 2 2
2008-01 29 1 2
2009-02 29 1 18 rows selectedSQL>