散分，sql语句，谢谢

[qdmis1223]

表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元。
应该怎么改改，谢谢各位

[qdmis1223]

错误的地方就是：如果在某个月几次消费的钱数都一样，则在最后的统计中没累加起来。

[DragonBill]

up

[hsmserver]

select 表1的工号，表1的姓名,[发生金额] = from select sum(金额) from 表2 group by decode(to_char(D1, 'mm') from 表

[qdmis123]

没有人帮忙改改吗？

[qdmis123]

..............
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(+);

[netrobo]

如果数据量非常大且使用频繁，建议增加一个汇总表，建立触发器，基础表数据变动时，自动更新汇总表。在统计时，只需按条件读出汇总表即可。

[hsmserver]

这样多影响速度啊？
我那种方式不行吗？
因为我没试
我记的原来那么写的
如有问题，你照葫芦画瓢改一下

[netrobo]

TO： hsmserver(撒哈拉之雨的悲伤)

楼主要求的好像是纵转向横的方式。

[lyguo]

up

[qdmis]

各位大侠,好好看看题目.

一个是员工表,一个是消费表.

根据月份统计的.

[qdmis]

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(+);

[xjspa]

create table emp(gh int primary key,
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]

楼主该结贴了吧
花了我半小时

[xjspa]

11 2004 25.0100 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 13.0000
22 2004 78.0000 .0000 55.0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000
33 2004 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 39.0000

[xjspa]

以上是运行结果