这样的语句怎么写?
表:
TA TB TC TD TE
A B C 2 120.00
A B D 5 600.00
A B1 C 3 360.00
A B1 D 4 480.00

查询出来的结果:
TA TB TC TD TE TF TG
A B C 2 120.00
A B D 5 600.00 7 720.00
A B1 C 3 360.00
A B1 D 4 480.00 7 7200.00
这样的SQL语句怎样写?

[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(
TA varchar(10),TB varchar(10),TC varchar(10),TD int,TE int
)

insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B ' , 'C ', 2, 120)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B ' , 'D ', 5, 600)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B1 ', 'C ', 3, 360)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B1 ' , 'D ', 4, 480)

select a.* , b.td,b.te from tb a,
(select ta,tb,sum(td) td,sum(te) te from tb group by ta,tb) b
where a.ta = b.ta and a.tb = b.tb

drop table tb

/*
TA TB TC TD TE td te
---------- ---------- ---------- ----------- ----------- ----------- -----------
A B C 2 120 7 720

A B D 5 600 7 720
A B1 C 3 360 7 840
A B1 D 4 480 7 840

（所影响的行数为 4 行）

*/
[解决办法]
那为什么另外两行不汇总呢？
是否只是把汇总纪录在最后一行呢
[解决办法]
上的效果和主出的果不一。



[解决办法]
drop table 表
create table 表(TA varchar(10),TB varchar(10),TC varchar(10),TD int,TE numeric(18,0))
insert into 表
select 'A ', 'B ', 'C ',2,120.00
union all select 'A ', 'B ', 'D ',5,600.00
union all select 'A ', 'B1 ', 'C ',3,360.00
union all select 'A ', 'B1 ', 'D ',4,480.00

select *,
case when tc = 'c ' then 0 else (select sum(td) from 表 t where t.tb=表.tb and t.tc <=表.tc) end as TF,
case when tc = 'c ' then 0 else (select sum(te) from 表 t where t.tb=表.tb and t.tc <=表.tc) end as TG
from 表

[解决办法]
--建议楼主使用这样的结果.
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(TA varchar(10),TB varchar(10),TC varchar(10),TD int,TE int)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B ' , 'C ', 2, 120)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B ' , 'D ', 5, 600)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B1 ', 'C ', 3, 360)
insert into tb(TA,TB,TC,TD,TE) values( 'A ', 'B1 ' , 'D ', 4, 480)
go


select * from tb
union all
select * from
(
select ta,tb,tc = '合计 ',sum(td) td,sum(te) te from tb group by ta,tb with rollup
) t
where ta is not null and tb is not null
order by ta , tb , tc

drop table tb

/*
TA TB TC TD TE
---------- ---------- ---------- ----------- -----------
A B C 2 120
A B D 5 600
A B 合计 7 720
A B1 C 3 360
A B1 D 4 480
A B1 合计 7 840

（所影响的行数为 6 行）
*/
[解决办法]
CREATE TABLE TB1
(
TA varchar(10),
TB varchar(10),
TC varchar(10),
TD INT,
TE NUMERIC(5,2)
)
INSERT INTO TB1
SELECT 'A ', 'B ', 'C ',2,120.00 UNION ALL
SELECT 'A ', 'B ', 'D ',5,600.00 UNION ALL
SELECT 'A ', 'B1 ', 'C ',3,360.00 UNION ALL
SELECT 'A ', 'B1 ', 'D ',4,480.00
GO
SELECT A.TA,A.TB,A.TC,A.TD,A.TE,CASE WHEN A.TE=(SELECT MAX(TE) FROM TB1 WHERE TA=A.TA AND TB = A.TB) THEN (SELECT SUM(TD) FROM TB1 WHERE TA=A.TA AND TB=A.TB) END TF,CASE WHEN A.TE=(SELECT MAX(TE) FROM TB1 WHERE TA=A.TA AND TB = A.TB) THEN (SELECT SUM(TE) FROM TB1 WHERE TA=A.TA AND TB=A.TB) END TG FROM TB1 A
--结果
TA TB TC TD TE TF TG
---------- ---------- ---------- ----------- --------------------------------------- ----------- ---------------------------------------

A B C 2 120.00 NULL NULL
A B D 5 600.00 7 720.00
A B1 C 3 360.00 NULL NULL
A B1 D 4 480.00 7 840.00

(4 行受影响)