有一表,内容如下
dw xsfl rs xs
aa 1 122 0.61
bb 1 100 0.63
cc 1 120 0.70
aa 2 90 0.62
bb 2 80 0.65
相同单位的合并计算,在jsgc中写下计算过程
dw rs jsgc je
aa 212 (122*0.61+90*0.62)*500=65110 65110
bb 180 (100*0.63+80*0.65)*500=57500 57500
cc 120 120*0.70*500=42000 42000
dw xsfl rs xs
aa 1 122 0.61
bb 1 100 0.63
cc 1 120 0.70
aa 2 90 0.62
bb 2 80 0.65
相同单位的合并计算,在jsgc中写下计算过程
dw rs jsgc je
aa 212 (122*0.61+90*0.62)*500=65110 65110
bb 180 (100*0.63+80*0.65)*500=57500 57500
cc 120 120*0.70*500=42000 42000
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2010-01-03 11:35:19
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (dw varchar(2),xsfl int,rs int,xs numeric(3,2))
INSERT INTO @tb
SELECT 'aa',1,122,0.61 UNION ALL
SELECT 'bb',1,100,0.63 UNION ALL
SELECT 'cc',1,120,0.70 UNION ALL
SELECT 'aa',2,90,0.62 UNION ALL
SELECT 'bb',2,80,0.65--SQL查询如下:SELECT dw,SUM(rs) AS rs,
SUM(rs*xs)*500 AS jsgc
FROM @tb
GROUP BY dw/*
dw rs jsgc
---- ----------- ---------------------------------------
aa 212 65110.00
bb 180 57500.00
cc 120 42000.00(3 行受影响)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2010-01-03 11:35:19
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (dw varchar(2),xsfl int,rs int,xs numeric(3,2))
INSERT INTO @tb
SELECT 'aa',1,122,0.61 UNION ALL
SELECT 'bb',1,100,0.63 UNION ALL
SELECT 'cc',1,120,0.70 UNION ALL
SELECT 'aa',2,90,0.62 UNION ALL
SELECT 'bb',2,80,0.65--SQL查询如下:SELECT dw,SUM(rs) AS rs,
'('+STUFF((SELECT '+'+RTRIM(rs)+'*'+RTRIM(xs)
FROM @tb
WHERE dw=A.dw
FOR XML PATH('')),1,1,'')+')*500' as jsgc,
SUM(rs*xs)*500 AS je
FROM @tb AS A
GROUP BY dw/*
dw rs jsgc je
aa 212 (122*0.61+90*0.62)*500 65110.00
bb 180 (100*0.63+80*0.65)*500 57500.00
cc 120 (120*0.70)*500 42000.00
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2010-01-03 11:35:19
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (dw varchar(2),xsfl int,rs int,xs numeric(3,2))
INSERT INTO @tb
SELECT 'aa',1,122,0.61 UNION ALL
SELECT 'bb',1,100,0.63 UNION ALL
SELECT 'cc',1,120,0.70 UNION ALL
SELECT 'aa',2,90,0.62 UNION ALL
SELECT 'bb',2,80,0.65--SQL查询如下:SELECT dw,SUM(rs) AS rs,
'('+STUFF((SELECT '+'+RTRIM(rs)+'*'+RTRIM(xs)
FROM @tb
WHERE dw=A.dw
FOR XML PATH('')),1,1,'')+')*500='+RTRIM(SUM(rs*xs)*500) as jsgc,
SUM(rs*xs)*500 AS je
FROM @tb AS A
GROUP BY dw/*
dw rs jsgc je
aa 212 (122*0.61+90*0.62)*500=65110.00 65110.00
bb 180 (100*0.63+80*0.65)*500=57500.00 57500.00
cc 120 (120*0.70)*500=42000.00 42000.00
*/
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2010-01-03 11:35:19
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (dw varchar(2),xsfl int,rs int,xs numeric(3,2))
INSERT INTO @tb
SELECT 'aa',1,122,0.61 UNION ALL
SELECT 'bb',1,100,0.63 UNION ALL
SELECT 'cc',1,120,0.70 UNION ALL
SELECT 'aa',2,90,0.62 UNION ALL
SELECT 'bb',2,80,0.65--SQL查询如下:
select * from @tbSELECT dw,SUM(rs) AS rs,
('('+STUFF((SELECT '+'+RTRIM(rs)+'*'+RTRIM(xs)
FROM @tb
WHERE dw=A.dw
FOR XML PATH('')),1,1,'')+')*500')+'='+CAST(SUM(rs*xs)*500 AS varchar(10)) as jsgc,
SUM(rs*xs)*500 AS je
FROM @tb AS A
GROUP BY dw/*
dw rs jsgc je
aa 212 (122*0.61+90*0.62)*500=65110.00 65110.00
bb 180 (100*0.63+80*0.65)*500= 57500.00 57500.00
cc 120 (120*0.70)*500=42000.00 42000.00
*/