直接把问题具体化吧:
现有一个表:
Tno Tname Tsum Tprice
001 mx_xx 1 1.35
001 mx_xx 50 0.58
001 mx_xx 100 0.25
002 mx_xd 1 1.8
002 mx_xd 200 0.2
003 mx_gd 30 1.2
现要更新成下列形式:sum(tsum*Tprice)
Tno Tname Tsum Tprice
001 mx_xx 1 1.35
001 mx_xx 50 0.58
001 mx_xx 100 0.25
001 - - 55.35
002 mx_xd 1 1.8
002 mx_xd 200 0.2
002 - - 41.8
003 mx_gd 30 1.2
003 - - 1.2
不规定只用一条语句完成~!
如果只需要查询成这样的结果,又是如何操作呢?
现有一个表:
Tno Tname Tsum Tprice
001 mx_xx 1 1.35
001 mx_xx 50 0.58
001 mx_xx 100 0.25
002 mx_xd 1 1.8
002 mx_xd 200 0.2
003 mx_gd 30 1.2
现要更新成下列形式:sum(tsum*Tprice)
Tno Tname Tsum Tprice
001 mx_xx 1 1.35
001 mx_xx 50 0.58
001 mx_xx 100 0.25
001 - - 55.35
002 mx_xd 1 1.8
002 mx_xd 200 0.2
002 - - 41.8
003 mx_gd 30 1.2
003 - - 1.2
不规定只用一条语句完成~!
如果只需要查询成这样的结果,又是如何操作呢?
INSERT INTO @T SELECT '001','mx_xx', 1,1.35
INSERT INTO @T SELECT '001','mx_xx', 50,0.58
INSERT INTO @T SELECT '001','mx_xx',100,0.25
INSERT INTO @T SELECT '002','mx_xd', 1, 1.8
INSERT INTO @T SELECT '002','mx_xd',200, 0.2
INSERT INTO @T SELECT '003','mx_gd', 30, 1.2SELECT
Tno ,
ISNULL(Tname,'-') AS Tname,
ISNULL(RTRIM(Tsum),'-') AS Tsum ,
(CASE WHEN Tprice IS NULL THEN SUM(Tprice*Tsum) ELSE Tprice END) AS Tprice
FROM
@T
GROUP BY
Tno,Tname,Tsum,Tprice WITH ROLLUP
HAVING
GROUPING(Tprice)=0 OR (GROUPING(Tno)=0 AND GROUPING(Tname)=1)/*
Tno Tname Tsum Tprice
---------- ---------- ------------ ----------
001 mx_xx 1 1.35
001 mx_xx 50 0.58
001 mx_xx 100 0.25
001 - - 55.35
002 mx_xd 1 1.80
002 mx_xd 200 0.20
002 - - 41.80
003 mx_gd 30 1.20
003 - - 36.00
*/
union
select Tno,'-','-',sum(Tsum*Tprice) from tablename group by tno
order by tno,case when tname = '-' then 1 else 0 end
select Tno,Tname,cast(Tsum as varchar) as Tsum,Tprice from @T
union
select Tno,'-','-',sum(Tsum*Tprice) from @T group by tno
order by tno,tname desc
drop table tb
gocreate table tb(
Tno varchar(10),
Tname varchar(10),
Tsum int,
Tprice decimal(18,2))insert into tb(Tno,Tname,Tsum,Tprice) values('001', 'mx_xx', 1 , 1.35)
insert into tb(Tno,Tname,Tsum,Tprice) values('001', 'mx_xx', 50, 0.58)
insert into tb(Tno,Tname,Tsum,Tprice) values('001', 'mx_xx', 100, 0.25)
insert into tb(Tno,Tname,Tsum,Tprice) values('002', 'mx_xd', 1 , 1.8)
insert into tb(Tno,Tname,Tsum,Tprice) values('002', 'mx_xd', 200, 0.2)
insert into tb(Tno,Tname,Tsum,Tprice) values('003', 'mx_gd', 30, 1.2)select t2.* from
(
select tno,tname,cast(tsum as varchar) as tsum,tprice from tb
union all
select tno,tname = '-' , tsum = '-' , tprice from
(
select tno,sum(tsum*tprice) as tprice from tb group by tno
) t1
) t2
order by tno , tname desc
drop table tb/*
tno tname tsum tprice
---------- ---------- ------------------------------ -------
001 mx_xx 1 1.35
001 mx_xx 50 .58
001 mx_xx 100 .25
001 - - 55.35
002 mx_xd 200 .20
002 mx_xd 1 1.80
002 - - 41.80
003 mx_gd 30 1.20
003 - - 36.00(所影响的行数为 9 行)*/
,ISNULL(LTRIM(Tsum),'-') AS Tsum
,SUM(Tsum*Tprice) AS Tprice
FROM @T
GROUP BY Tno,Tname,Tsum WITH ROLLUP
HAVING GROUPING(Tname) <> 1在查询分析器中针对不明的SQL关键词搜索查看一下呗