如果我们有这样的表格：
Grp Value Grp1 2 Grp1 5 Grp1 3 Grp2 3 Grp2 -5 Grp2 -2 Grp3 4 Grp3 0 Grp3 1 Grp4 -2 Grp4 -4 Grp5 7 Grp5 NULL Grp6 NULL Grp6 NULL Grp7 -1 Grp7 10
我们如何分组/乘以得到这个？
GrpID Value Grp1 30 Grp2 30 Grp3 0 Grp4 8 Grp5 7 Grp6 NULL Grp7 -10
解决方法
乘以行值与添加行值的对数相同
诀窍是处理零和空值.
好的,现在检查
DECLARE @foo TABLE (GrpID varchar(10),Value float)
INSERt @foo (GrpID,Value)
SELECT 'Grp1',2
UNION ALL SELECT 'Grp1',5
UNION ALL SELECT 'Grp1',3
UNION ALL SELECT 'Grp2',-5
UNION ALL SELECT 'Grp2',-2
UNION ALL SELECT 'Grp3',4
UNION ALL SELECT 'Grp3',0
UNION ALL SELECT 'Grp3',1
UNION ALL SELECT 'Grp4',-2
UNION ALL SELECT 'Grp4',-4
UNION ALL SELECT 'Grp5',7
UNION ALL SELECT 'Grp5',NULL
UNION ALL SELECT 'Grp6',NULL
UNION ALL SELECT 'Grp7',-1
UNION ALL SELECT 'Grp7',10
SELECT
GrpID,CASE
WHEN MinVal = 0 THEN 0
WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
ELSE EXP(ABSMult)
END
FROM
(
SELECT
GrpID,--log of +ve row values
SUM(LOG(ABS(NULLIF(Value,0)))) AS ABSMult,--count of -ve values. Even = +ve result.
SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,--anything * zero = zero
MIN(ABS(Value)) AS MinVal
FROM
@foo
GROUP BY
GrpID
) foo