17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT substr(TBD.f_Staticdaytime, 1, 4) || '年' ||
TO_CHAR(TO_DATE(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
nvl(sum(decode(substr(TBD.f_regioncode, 1, 4),
'4201',
TBD.F_DEADPOPNUM,
0)) - lag(sum(decode(substr(TBD.f_regioncode, 1, 4),
'4201',
TBD.F_DEADPOPNUM,
0)),
1,
0)
over(partition by
to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy')
order by TBD.f_Staticdaytime),
0) data0,
nvl(sum(decode(substr(TBD.f_regioncode, 1, 4),
'4202',
TBD.F_DEADPOPNUM,
0)) - lag(sum(decode(substr(TBD.f_regioncode, 1, 4),
'4202',
TBD.F_DEADPOPNUM,
0)),
1,
0)
over(partition by
to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy')
order by TBD.f_Staticdaytime),
0) data1
FROM TB_BAS_COUDAYHAZARDDATA TBD
WHERE substr(TBD.f_staticdaytime, 6) IN
('04-30', '05-31', '01-31', '02-28', '02-29', '03-31')
AND substr(TBD.f_Staticdaytime, 1, 4) IN ('2010')
AND TBD.f_hazardtype = '1'
AND substr(TBD.f_regioncode, 1, 4) IN ('4201', '4202')
GROUP BY TO_CHAR(TO_DATE(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
WITH tab AS(
SELECT '2010年1月' dates, 3 data0,31 data1 FROM dual UNION ALL
SELECT '2010年2月', 9, -31 FROM dual UNION ALL
SELECT '2010年3月', 9, 27 FROM dual UNION ALL
SELECT '2010年4月', -21, 2 FROM dual UNION ALL
SELECT '2010年5月', 39, 31 FROM dual
)
SELECT '武汉市' city, Sum(Decode(Sign(data0),-1,0,data0)) data FROM tab
UNION ALL
SELECT '黄石市',Sum(Decode(Sign(data1),-1,0,data1)) FROM tab
CITY DATA
--------------
武汉市 60
黄石市 91