Sql求助！！

[u010958237]

有一张表,结构如下:
加工编号 申请日期 供应商 加工金额
w11 2010-1-1 A 30
w11 2010-1-1 b 40
w12 2010-1-1 b 11
w11 2010-1-1 c 22
w12 2010-1-1 d 30
w12 2010-1-1 A 33
注：加工编号不是Id
想要的结果是这样的,所有加工编号相同的加工金额,添加一列显示总金额
加工编号 申请日期 供应商 加工金额 总金额
w11 2010-1-1 A 30 92
w11 2010-1-1 b 40 92
w12 2010-1-1 b 11 74
w11 2010-1-1 c 22 92
w12 2010-1-1 d 30 74
w12 2010-1-1 A 33 74
不知道大神们看懂没？

[天-笑]

;WITH T as (
select 'w11' as Code,'2010-1-1' as Date,'A' as S,30 as M union all 
select 'w11' as Code,'2010-1-1' as Date,'b' as S,40 as M union all
select 'w12' as Code,'2010-1-1' as Date,'b' as S,11 as M union all
select 'w11' as Code,'2010-1-1' as Date,'c' as S,22 as M union all
select 'w12' as Code,'2010-1-1' as Date,'d' as S,30 as M union all
select 'w12' as Code,'2010-1-1' as Date,'a' as S,33 as M
)
select * from T a 
left join (
select Code,sum(M) as Mon  from T group by Code
) b on a.code = b.code 
/*
Code Date     S    M           Code Mon
---- -------- ---- ----------- ---- -----------
w11  2010-1-1 A    30          w11  92
w11  2010-1-1 b    40          w11  92
w12  2010-1-1 b    11          w12  74
w11  2010-1-1 c    22          w11  92
w12  2010-1-1 d    30          w12  74
w12  2010-1-1 a    33          w12  74

(6 行受影响)


*/

[Andy__Huang]

select a.*,b.总金额 
from tb a
left join (select 加工编号,SUM(加工金额) as 总金额  from tb group by 加工编号)b
on a.加工编号=b.加工编号

[Andy__Huang]

;with tb(加工编号,申请日期,供应商,加工金额 ) as
(
select 'w11','2010-1-1','A',30
union all select 'w11','2010-1-1','b',40
union all select 'w12','2010-1-1','b',11
union all select 'w11','2010-1-1','c',22
union all select 'w12','2010-1-1','d',30
union all select 'w12','2010-1-1','A',33
)
select a.*,b.总金额 
from tb a
left join (select 加工编号,SUM(加工金额) as 总金额  from tb group by 加工编号)b
on a.加工编号=b.加工编号

/*
加工编号	申请日期	供应商	加工金额	总金额
---------------------------------------------------
w11	2010-1-1	A	30	92
w11	2010-1-1	b	40	92
w12	2010-1-1	b	11	74
w11	2010-1-1	c	22	92
w12	2010-1-1	d	30	74
w12	2010-1-1	A	33	74
*/

[-Tracy-McGrady-]

;with tb(加工编号,申请日期,供应商,加工金额 ) as ( select 'w11','2010-1-1','A',30 union all select 'w11','2010-1-1','b',40 union all select 'w12','2010-1-1','b',11 union all select 'w11','2010-1-1','c',22 union all select 'w12','2010-1-1','d',30 union all select 'w12','2010-1-1','A',33 ) select a.*,b.总金额 from tb a left join (select 加工编号,SUM(加工金额) as 总金额 from tb group by 加工编号)b on a.加工编号=b.加工编号

[u010958237]

引用 1 楼 shmilywcd 的回复:

;WITH T as (
select 'w11' as Code,'2010-1-1' as Date,'A' as S,30 as M union all 
select 'w11' as Code,'2010-1-1' as Date,'b' as S,40 as M union all
select 'w12' as Code,'2010-1-1' as Date,'b' as S,11 as M union all
select 'w11' as Code,'2010-1-1' as Date,'c' as S,22 as M union all
select 'w12' as Code,'2010-1-1' as Date,'d' as S,30 as M union all
select 'w12' as Code,'2010-1-1' as Date,'a' as S,33 as M
)
select * from T a 
left join (
select Code,sum(M) as Mon  from T group by Code
) b on a.code = b.code 
/*
Code Date     S    M           Code Mon
---- -------- ---- ----------- ---- -----------
w11  2010-1-1 A    30          w11  92
w11  2010-1-1 b    40          w11  92
w12  2010-1-1 b    11          w12  74
w11  2010-1-1 c    22          w11  92
w12  2010-1-1 d    30          w12  74
w12  2010-1-1 a    33          w12  74

(6 行受影响)


*/

谢谢

[u010958237]

引用 4 楼 yangsh0722 的回复:

;with tb(加工编号,申请日期,供应商,加工金额 ) as ( select 'w11','2010-1-1','A',30 union all select 'w11','2010-1-1','b',40 union all select 'w12','2010-1-1','b',11 union all select 'w11','2010-1-1','c',22 union all select 'w12','2010-1-1','d',30 union all select 'w12','2010-1-1','A',33 ) select a.*,b.总金额 from tb a left join (select 加工编号,SUM(加工金额) as 总金额 from tb group by 加工编号)b on a.加工编号=b.加工编号

谢谢

[u010958237]

引用 2 楼 hdhai9451 的回复:

select a.*,b.总金额 
from tb a
left join (select 加工编号,SUM(加工金额) as 总金额  from tb group by 加工编号)b
on a.加工编号=b.加工编号

谢谢