求SQL语句在一表内按某字段分组统计数据
直接把问题具体化吧：
现有一个表：
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
不规定只用一条语句完成~！
如果只需要查询成这样的结果，又是如何操作呢？
[解决办法]
DECLARE @T TABLE(Tno VARCHAR(10),Tname VARCHAR(10),Tsum INT,Tprice NUMERIC(5,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.2
SELECT
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
*/
[解决办法]
--修改一下：
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
