sid sname amoney bmoney tdate
-----------------------------------------------------------
1 广州 10 8 2011-01
1 广州 14 13 2011-01
2 上海 13 12 2011-01
2 上海 18 16 2011-01
1 广州 14 10 2011-02
想得到以下结果sid sname 1月amoney 1月bmoney 2月amoney 2月bmoney
---------------------------------------------------------------
1 广州 24 21 14 13
2 上海 31 28 0 0
-----------------------------------------------------------
1 广州 10 8 2011-01
1 广州 14 13 2011-01
2 上海 13 12 2011-01
2 上海 18 16 2011-01
1 广州 14 10 2011-02
想得到以下结果sid sname 1月amoney 1月bmoney 2月amoney 2月bmoney
---------------------------------------------------------------
1 广州 24 21 14 13
2 上海 31 28 0 0
sname,
max(decode(to_char(tdate,'mm'),'01',amoney,0)) "1月amoney",
max(decode(to_char(tdate,'mm'),'01',bmoney,0)) "1月bmoney",
max(decode(to_char(tdate,'mm'),'02',amoney,0)) "2月amoney",
max(decode(to_char(tdate,'mm'),'02',bmoney,0)) "1月bmoney"
from tablename
group by sid,sname
select 1 sid,'广州' sname,10 amoney,8 bmoney,to_date('2011-01','yyyy-mm') tdate
from dual union all
select 1, '广州' ,14, 13, to_date('2011-01','yyyy-mm') from dual union all
select 2, '上海' ,13, 12, to_date('2011-01','yyyy-mm') from dual union all
select 2, '上海' ,18, 16, to_date('2011-01','yyyy-mm') from dual union all
select 1, '广州' ,14, 10, to_date('2011-02','yyyy-mm') from dual)
--以上为提供数据的语句
select sid,
sname,
max(decode(to_char(tdate,'mm'),'01',amoney,0)) "1月amoney",
max(decode(to_char(tdate,'mm'),'01',bmoney,0)) "1月bmoney",
max(decode(to_char(tdate,'mm'),'02',amoney,0)) "2月amoney",
max(decode(to_char(tdate,'mm'),'02',bmoney,0)) "1月bmoney"
from tb
group by sid,sname
SID SNAM 1月amoney 1月bmoney 2月amoney 1月bmoney
---------- ---- ---------- ---------- ---------- ----------
1 广州 14 13 14 10
2 上海 18 16 0 0
SQL> with city as (
2 select 1 as sid,'广州' as sname,10 as amoney,8 as bmoney,'2011-01' as tdate from dual union all
3 select 1 as sid,'广州' as sname,14 as amoney,13 as bmoney,'2011-01' as tdate from dual union all
4 select 2 as sid,'上海' as sname,13 as amoney,12 as bmoney,'2011-01' as tdate from dual union all
5 select 2 as sid,'上海' as sname,18 as amoney,16 as bmoney,'2011-01' as tdate from dual union all
6 select 1 as sid,'广州' as sname,10 as amoney,10 as bmoney,'2011-02' as tdate from dual
7 )
8 select sid,sname,
9 sum(decode(tdate,'2011-01',amoney,0)) as amoney,
10 sum(decode(tdate,'2011-02',bmoney,0)) as bmoney
11 from city group by sid,sname;
SID SNAME AMONEY BMONEY
---------- ------ ---------- ----------
1 广州 24 10
2 上海 31 0
--------------------------一行显示12个月统计的金额
sname,
max(decode(to_char(tdate,'mm'),'01',amoney,0)) "1月amoney",
max(decode(to_char(tdate,'mm'),'01',bmoney,0)) "1月bmoney",
max(decode(to_char(tdate,'mm'),'02',amoney,0)) "2月amoney",
max(decode(to_char(tdate,'mm'),'02',bmoney,0)) "2月bmoney",
max(decode(to_char(tdate,'mm'),'03',amoney,0)) "3月amoney",
max(decode(to_char(tdate,'mm'),'03',bmoney,0)) "3月bmoney",
max(decode(to_char(tdate,'mm'),'04',amoney,0)) "4月amoney",
max(decode(to_char(tdate,'mm'),'04',bmoney,0)) "4月bmoney",
max(decode(to_char(tdate,'mm'),'05',amoney,0)) "5月amoney",
max(decode(to_char(tdate,'mm'),'05',bmoney,0)) "5月bmoney",
max(decode(to_char(tdate,'mm'),'06',amoney,0)) "6月amoney",
max(decode(to_char(tdate,'mm'),'06',bmoney,0)) "6月bmoney",
......
from tb
group by sid,sname
select 1 sid, '广州'sname,10 amoney, 8 bmoney,to_date('2011-01','yyyy-mm') tdate from dual union all
select 1,'广州',14,13,to_date('2011-01','yyyy-mm') from dual union all
select 2,'上海',13,12,to_date('2011-01','yyyy-mm') from dual union all
select 2,'上海',18,16,to_date('2011-01','yyyy-mm') from dual union all
select 1,'广州',14,10,to_date('2011-02','yyyy-mm') from dual
)
select sid, sname,
sum(decode(to_char(tdate,'mm'),'01',amoney,0)) "1月amoney",
sum(decode(to_char(tdate,'mm'),'01',bmoney,0)) "1月bmoney",
sum(decode(to_char(tdate,'mm'),'02',amoney,0)) "2月amoney",
sum(decode(to_char(tdate,'mm'),'02',bmoney,0)) "2月bmoney"
from tb group by sid,sname;
with tb as (
select 1 sid, '广州'sname,10 amoney, 8 bmoney,to_date('2011-01','yyyy-mm') tdate from dual union all
select 1,'广州',14,13,to_date('2011-01','yyyy-mm') from dual union all
select 2,'上海',13,12,to_date('2011-01','yyyy-mm') from dual union all
select 2,'上海',18,16,to_date('2011-01','yyyy-mm') from dual union all
select 1,'广州',14,10,to_date('2011-02','yyyy-mm') from dual
)
select
sum(decode(to_char(tdate,'mm'),'01',amoney,0)) "1月amoney",
sum(decode(to_char(tdate,'mm'),'01',bmoney,0)) "1月bmoney",
sum(decode(to_char(tdate,'mm'),'02',amoney,0)) "2月amoney",
sum(decode(to_char(tdate,'mm'),'02',bmoney,0)) "2月bmoney"
from tb group by sid,sname;------------------------------------------------1月amoney 1月bmoney 2月amoney 2月bmoney
24 21 14 10
31 28 0 0
你少了group by吧~~~