现有表:
ajqk varchar2(20);//案件情况, 破案,立案,移诉
indate date;// 录入日期
sf //省份;
sssx //所属市
qx //所属区县如何以下格式列出
立案 移诉 破案
当月|上年同期|今年累计|上年累计 当月|上年同期|今年累计|上年累计 当月|上年同期|今年累计|上年累计
杭州:
宁波:
温州:
谁来指导一下。非常感谢
ajqk varchar2(20);//案件情况, 破案,立案,移诉
indate date;// 录入日期
sf //省份;
sssx //所属市
qx //所属区县如何以下格式列出
立案 移诉 破案
当月|上年同期|今年累计|上年累计 当月|上年同期|今年累计|上年累计 当月|上年同期|今年累计|上年累计
杭州:
宁波:
温州:
谁来指导一下。非常感谢
--indate date;// 录入日期
--sf //省份;
--sssx //所属市
--qx //所属区县 with temp as
(
select '破案' ajqk, date'2009-10-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-10-02' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-10-03' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-10-04' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-10-05' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-09-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-09-02' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-08-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2009-07-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-10-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-10-02' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-10-03' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-10-04' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-08-01' indate, '福州' sf from dual
union all
select '破案' ajqk, date'2008-07-01' indate, '福州' sf from dual
)
select distinct sf,
sum(case when sa=to_char(sysdate,'yyyy-mm') then ra else 0 end) 当月累计,
sum(case when sa=to_char(sysdate,'yyyy')-1||'-'||to_char(sysdate,'mm') then ra else 0 end) 去年同期累计,
sum(case when sa=to_char(sysdate,'yyyy') then rb else 0 end) 今年累计,
sum(case when sa=to_char(to_number(to_char(sysdate,'yyyy'))-1) then rb else 0 end) 去年累计
from
(
select distinct sf,
to_char(indate,'yyyy-mm') sa,
sum(decode(ajqk,'破案',1,0)) over(partition by to_char(indate,'yyyy-mm')) ra,0 rb
from temp
where to_char(indate,'mm')=to_char(sysdate,'mm')
union all
select distinct sf,
to_char(indate,'yyyy') sa,0 ra,
sum(decode(ajqk,'破案',1,0)) over(partition by to_char(indate,'yyyy')) ra
from temp
)
group by sf
--result:福州 5 4 9 6