select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区1
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
这个SQL文的查询结果是:
dates 地区1
11月 1
12月 61
1月 40
2月 70select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
这个SQL文的查询结果是:
dates 地区2
11月 7
12月 46
1月 35
这两个SQL文查询的东西都一样,都是查询某个地方某月的一些数据,唯一不一样的是
substr(TBD.f_regioncode, 1, 6) in ('XXXXX')这个条件,
我想把它们合成一个,结果是:
dates 地区1 地区2
11月 1 7
12月 61 46
1月 40 35
2月 70 0
这样的话我可以比较容易比较这个2各地区个月的数据,
麻烦大侠们指导一下,谢谢了。
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区1
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
这个SQL文的查询结果是:
dates 地区1
11月 1
12月 61
1月 40
2月 70select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
这个SQL文的查询结果是:
dates 地区2
11月 7
12月 46
1月 35
这两个SQL文查询的东西都一样,都是查询某个地方某月的一些数据,唯一不一样的是
substr(TBD.f_regioncode, 1, 6) in ('XXXXX')这个条件,
我想把它们合成一个,结果是:
dates 地区1 地区2
11月 1 7
12月 61 46
1月 40 35
2月 70 0
这样的话我可以比较容易比较这个2各地区个月的数据,
麻烦大侠们指导一下,谢谢了。
F_STATICDAYTIME F_DEADPOPNUM F_HAZARDTYPE F_REGIONNAME F_REGIONCODE
1 2009-11-27 2 1 北京市 110000000000
2 2009-11-30 3 1 天津市 120000000000
3 2009-11-30 4 1 天津市 120000000000
4 2009-11-30 1 1 北京市 110000000000
5 2009-12-30 21 1 天津市 120000000000
6 2009-12-31 29 1 北京市 110000000000
7 2009-12-31 35 1 天津市 120000000000
8 2009-12-31 33 1 北京市 110000000000
9 2009-12-31 18 1 天津市 120000000000
10 2010-01-28 23 1 北京市 110000000000
11 2010-01-31 35 1 天津市 120000000000
12 2010-01-31 40 1 北京市 110000000000
13 2010-02-28 50 1 北京市 110000000000
14 2010-02-28 60 1 北京市 110000000000
15 2010-02-20 70 1 北京市 110000000000
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
case when substr(TBD.f_regioncode, 1, 6)='110000' then sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) end 地区1,
case when substr(TBD.f_regioncode, 1, 6)='120000' then sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) end 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
这个条件去掉
--比如:
WITH tab1 AS(
SELECT '11月' dates, 1 address1 FROM dual UNION ALL
SELECT '12月', 61 FROM dual UNION ALL
SELECT '1月', 40 FROM dual UNION ALL
SELECT '2月', 70 FROM dual
),
tab2 AS(
SELECT '11月' dates, 7 address2 FROM dual UNION ALL
SELECT '12月', 46 FROM dual UNION ALL
SELECT '1月', 35 FROM dual
)
SELECT a.dates,Nvl(a.address1,0) address1,Nvl(b.address2,0) address2
FROM tab1 a full join tab2 b
on a.dates=b.dates--结果:
DATES ADDRESS1 ADDRESS2
-------------------------------------
11月 1 7
12月 61 46
1月 40 35
2月 70 0
sum(TBD1.f_Deadpopnum) - lag(sum(TBD1.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD1.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD1.f_Staticdaytime) 地区1
sum(TBD2.f_Deadpopnum) - lag(sum(TBD2.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD2.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD2.f_Staticdaytime) 地区2
from TB_BAS_PRODAYHAZARDDATA TBD1 join TB_BAS_PRODAYHAZARDDATA TBD2 on TBD1.f_staticdaytime = TBD2.f_staticdaytime
where substr(TBD1.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD1.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD1.f_hazardtype = '1'
and (substr(TBD1.f_regioncode, 1, 6) in ('110000')
or substr(TBD2.f_regioncode, 1, 6) in ('120000'))
group by to_char(to_date(TBD1.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
-----
试试吧,没你的数据库表,不知道执行下来对不对,思路应该可以。
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
CASE WHEN substr(TBD.f_regioncode, 1, 6)='110000'
THEN sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
END 地区1 ,
CASE WHEN substr(TBD.f_regioncode, 1, 6)='120000'
THEN sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
END 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),TBD.f_Staticdaytime
--最原始的方法
select dates,max(地区1),max(地区2)
from (select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区1,null 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
UNION ALL
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
null 地区1,
sum(TBD.f_Deadpopnum) - lag(sum(TBD.f_Deadpopnum), 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime) 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime)
group by dates
--decodeselect to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',TBD.f_Deadpopnum,0))-
lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',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) 地区1,
sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',TBD.f_Deadpopnum,0))-
lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',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) 地区1,
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000','120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_Staticdaytime
朋友,我用这种方法试验了,因为添加了case when的条件,所以最后group by 的条件里要添加substr(TBD.f_regioncode, 1, 6) 或者 TBD.f_regioncode,但是执行结果不对了,结果如下:1 11月 7
2 11月 -6
3 12月 52
4 12月 9
5 1月 35
6 1月 5
7 2月 70
2 12月 61 46
3 1月 40 35
4 2月 70 -35学习了,谢谢,好好研究一下你的SQL文。O(∩_∩)O~
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
Sum(CASE WHEN substr(TBD.f_regioncode, 1, 6)='110000'
THEN TBD.f_Deadpopnum - lag(TBD.f_Deadpopnum, 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
ELSE 0)
END 地区1 ,
Sum(CASE WHEN substr(TBD.f_regioncode, 1, 6)='120000'
THEN TBD.f_Deadpopnum - lag(TBD.f_Deadpopnum, 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
ELSE 0)
END 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),TBD.f_Staticdaytime
--哎 上面括号打错了
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
Sum(CASE WHEN substr(TBD.f_regioncode, 1, 6)='110000'
THEN TBD.f_Deadpopnum - lag(TBD.f_Deadpopnum, 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
ELSE 0
END) 地区1 ,
Sum(CASE WHEN substr(TBD.f_regioncode, 1, 6)='120000'
THEN TBD.f_Deadpopnum - lag(TBD.f_Deadpopnum, 1, 0) over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime)
ELSE 0
END) 地区2
from TB_BAS_PRODAYHAZARDDATA TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),TBD.f_Staticdaytime
(
select '2009-11-27' F_STATICDAYTIME,2 F_DEADPOPNUM,1 F_HAZARDTYPE,'北京市' F_REGIONNAME,'110000000000' F_REGIONCODE from dual union all
select '2009-11-30',3,1,'天津市','120000000000' from dual union all
select '2009-11-30',4,1,'天津市','120000000000' from dual union all
select '2009-11-30',1,1,'北京市','110000000000' from dual union all
select '2009-12-30',21,1,'天津市','120000000000' from dual union all
select '2009-12-31',29,1,'北京市','110000000000' from dual union all
select '2009-12-31',35,1,'天津市','120000000000' from dual union all
select '2009-12-31',33,1,'北京市','110000000000' from dual union all
select '2009-12-31',18,1,'天津市','120000000000' from dual union all
select '2010-01-28',23,1,'北京市','110000000000' from dual union all
select '2010-01-31',35,1,'天津市','120000000000' from dual union all
select '2010-01-31',40,1,'北京市','110000000000' from dual union all
select '2010-02-28',50,1,'北京市','110000000000' from dual union all
select '2010-02-28',60,1,'北京市','110000000000' from dual union all
select '2010-02-20',70,1,'北京市','110000000000' from dual)
select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
nvl(sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',TBD.f_Deadpopnum,0))-
lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',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) 地区1,
nvl(sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',TBD.f_Deadpopnum))-
lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',TBD.f_Deadpopnum)), 1, 0)
over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime),0) 地区1
from tb TBD
where substr(TBD.f_staticdaytime, 6) in
('01-31', '02-28', '02-29', '11-30', '12-31')
and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
and TBD.f_hazardtype = '1'
and substr(TBD.f_regioncode, 1, 6) in ('110000','120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
TBD.f_StaticdaytimeSQL> with tb as
2 (
3 select '2009-11-27' F_STATICDAYTIME,2 F_DEADPOPNUM,1 F_HAZARDTYPE,'北京市' F_REGIONNAME,'110000000000' F_REGIONCODE from dual union all
4 select '2009-11-30',3,1,'天津市','120000000000' from dual union all
5 select '2009-11-30',4,1,'天津市','120000000000' from dual union all
6 select '2009-11-30',1,1,'北京市','110000000000' from dual union all
7 select '2009-12-30',21,1,'天津市','120000000000' from dual union all
8 select '2009-12-31',29,1,'北京市','110000000000' from dual union all
9 select '2009-12-31',35,1,'天津市','120000000000' from dual union all
10 select '2009-12-31',33,1,'北京市','110000000000' from dual union all
11 select '2009-12-31',18,1,'天津市','120000000000' from dual union all
12 select '2010-01-28',23,1,'北京市','110000000000' from dual union all
13 select '2010-01-31',35,1,'天津市','120000000000' from dual union all
14 select '2010-01-31',40,1,'北京市','110000000000' from dual union all
15 select '2010-02-28',50,1,'北京市','110000000000' from dual union all
16 select '2010-02-28',60,1,'北京市','110000000000' from dual union all
17 select '2010-02-20',70,1,'北京市','110000000000' from dual)
18 select to_char(to_date(TBD.f_staticdaytime, 'yyyy-MM-dd'), 'mon') dates,
19 nvl(sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',TBD.f_Deadpopnum,0))-
20 lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'110000',TBD.f_Deadpopnum,0)), 1, 0)
21 over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime),0) 地区1,
22 nvl(sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',TBD.f_Deadpopnum))-
23 lag(sum(decode(substr(TBD.f_regioncode, 1, 6),'120000',TBD.f_Deadpopnum)), 1, 0)
24 over(partition by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'yyyy') order by TBD.f_Staticdaytime),0) 地区1
25 from tb TBD
26 where substr(TBD.f_staticdaytime, 6) in
27 ('01-31', '02-28', '02-29', '11-30', '12-31')
28 and substr(TBD.f_Staticdaytime, 1, 4) in ('2009', '2010')
29 and TBD.f_hazardtype = '1'
30 and substr(TBD.f_regioncode, 1, 6) in ('110000','120000') group by to_char(to_date(TBD.f_Staticdaytime, 'yyyy-MM-dd'), 'mon'),
31 TBD.f_Staticdaytime
32 /
DATES 地区1 地区1
-------- ---------- ----------
11月 1 7
12月 61 46
1月 40 35
2月 70 0
SQL>