表1:
工号 姓名
11 aaa
22 bbb
表2:
工号 金额 时间
11 12.01 2004-1
11 13 2004-1
22 55 2004-3
22 78 2004-1
实现的功能是:
工号 姓名 发生金额
1月 2月 3月 4月。
11 aaa 25.01 0 0 0。
22 bbb 78 0 55 0。 select a.*,b.* from yb1 a,(
select C1,sum(C4),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),
sum(m8),sum(m9),sum(m10),sum(m11),sum(m12) from(
select C1,C4,
max(decode(to_char(D1, 'mm'),'01',C4,0)) m1,
max(decode(to_char(D1, 'mm'),'02',C4,0)) m2,
max(decode(to_char(D1, 'mm'),'03',C4,0)) m3,
max(decode(to_char(D1, 'mm'),'04',C4,0)) m4,
max(decode(to_char(D1, 'mm'),'05',C4,0)) m5,
max(decode(to_char(D1, 'mm'),'06',C4,0)) m6,
max(decode(to_char(D1, 'mm'),'07',C4,0)) m7,
max(decode(to_char(D1, 'mm'),'08',C4,0)) m8,
max(decode(to_char(D1, 'mm'),'09',C4,0)) m9,
max(decode(to_char(D1, 'mm'),'10',C4,0)) m10,
max(decode(to_char(D1, 'mm'),'11',C4,0)) m11,
max(decode(to_char(D1, 'mm'),'12',C4,0)) m12
from(
select C1,sum(C4) as C4,D1
from yb2 group by C1,D1)
group by C1,C4
)
group by C1) b
where a.C1=b.C1(+);
这个语句能解决,可如果一个人在三月消费3次,每次10元,则在最后的统计中三月只消费了10元。
应该怎么改改,谢谢各位
工号 姓名
11 aaa
22 bbb
表2:
工号 金额 时间
11 12.01 2004-1
11 13 2004-1
22 55 2004-3
22 78 2004-1
实现的功能是:
工号 姓名 发生金额
1月 2月 3月 4月。
11 aaa 25.01 0 0 0。
22 bbb 78 0 55 0。 select a.*,b.* from yb1 a,(
select C1,sum(C4),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),
sum(m8),sum(m9),sum(m10),sum(m11),sum(m12) from(
select C1,C4,
max(decode(to_char(D1, 'mm'),'01',C4,0)) m1,
max(decode(to_char(D1, 'mm'),'02',C4,0)) m2,
max(decode(to_char(D1, 'mm'),'03',C4,0)) m3,
max(decode(to_char(D1, 'mm'),'04',C4,0)) m4,
max(decode(to_char(D1, 'mm'),'05',C4,0)) m5,
max(decode(to_char(D1, 'mm'),'06',C4,0)) m6,
max(decode(to_char(D1, 'mm'),'07',C4,0)) m7,
max(decode(to_char(D1, 'mm'),'08',C4,0)) m8,
max(decode(to_char(D1, 'mm'),'09',C4,0)) m9,
max(decode(to_char(D1, 'mm'),'10',C4,0)) m10,
max(decode(to_char(D1, 'mm'),'11',C4,0)) m11,
max(decode(to_char(D1, 'mm'),'12',C4,0)) m12
from(
select C1,sum(C4) as C4,D1
from yb2 group by C1,D1)
group by C1,C4
)
group by C1) b
where a.C1=b.C1(+);
这个语句能解决,可如果一个人在三月消费3次,每次10元,则在最后的统计中三月只消费了10元。
应该怎么改改,谢谢各位
max(decode(to_char(D1, 'mm'),'12',C4,0)) m12
from(
select C1,sum(C4) as C4,D1
from yb2 group by C1,D1)
group by C1,C4 // 改为group by C1,d1,C4 结果是对的,不知道这样对不对》?
)
group by C1) b
where a.C1=b.C1(+);
from(
select C1,sum(C4) as C4,D1
from yb2 group by C1,D1)
group by C1,C4 // 改为group by C1,d1,C4 结果是对的,不知道这样对不对》???????????
)
group by C1) b
where a.C1=b.C1(+);
name char(20))
go
insert into emp values(11,'aaa')
insert into emp values(22,'bbb')
insert into emp values(33,'ccc')
go
create table wage(gh int ,wages money,pay_date varchar(10))
insert into wage values(11,12.01,'2004-1')
insert into wage values(22,78,'2004-1')
insert into wage values(22,55,'2004-3')
insert into wage values(11,13,'2004-1')
insert into wage values(11,13,'2004-12')
insert into wage values(33,13,'2004-12')
insert into wage values(33,13,'2004-12')
insert into wage values(33,13,'2004-12')
go
--建立一个试图吧
create view v_static_wages
(gh,[name],[year],[month],wages)
as
select e.gh,e.name,substring(w.pay_date,1,4),substring(w.pay_date,6,2),w.wages from emp as e
inner join wage as w on e.gh=w.gh
go
select gh,[year],
sum(case [month] when '1' then wages else 0 end) as [1月],
sum(case [month] when '2' then wages else 0 end) as [2月],
sum(case [month] when '3' then wages else 0 end) as [3月],
sum(case [month] when '4' then wages else 0 end) as [4月],
sum(case [month] when '5' then wages else 0 end) as [5月],
sum(case [month] when '6' then wages else 0 end) as [6月],
sum(case [month] when '7' then wages else 0 end) as [7月],
sum(case [month] when '8' then wages else 0 end) as [8月],
sum(case [month] when '9' then wages else 0 end) as [9月],
sum(case [month] when '10' then wages else 0 end) as [10月],
sum(case [month] when '11' then wages else 0 end) as [11月],
sum(case [month] when '12' then wages else 0 end) as [12月]
from v_static_wages
group by gh,[year]楼主该结贴了吧
花了我半小时