;WITH T AS(
SELECT ROW_NUMBER()OVER(PARTITION BY Id ORDER BY GETDATE())RN
,*
FROM tba
)
,CTE AS(
SELECT Id,Adjust,RN FROM T
WHERE RN=1
UNION ALL
SELECT T1.Id,T1.Adjust*T2.Adjust,T1.RN
FROM T T1
JOIN CTE T2 ON T1.Id=T2.Id
AND T1.RN=T2.RN+1
)
SELECT Id,MAX(Adjust)Adjust FROM CTE
GROUP BY Id没有累乘,可以考虑用递归
SELECT ROW_NUMBER()OVER(PARTITION BY Id ORDER BY GETDATE())RN
,*
FROM tba
)
,CTE AS(
SELECT Id,Adjust,RN FROM T
WHERE RN=1
UNION ALL
SELECT T1.Id,T1.Adjust*T2.Adjust,T1.RN
FROM T T1
JOIN CTE T2 ON T1.Id=T2.Id
AND T1.RN=T2.RN+1
)
SELECT Id,MAX(Adjust)Adjust FROM CTE
GROUP BY Id没有累乘,可以考虑用递归
SET @sql = '';WITH /* 测试数据
tba(Id, Adjust) AS (
SELECT 1,10 UNION ALL
SELECT 1,20 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2,10 UNION ALL
SELECT 2,20
),*/
t1(id,expr) AS (
SELECT id,
Stuff((SELECT '*'+Convert(varchar(11),adjust)
FROM tba AS b
WHERE b.id = a.id
FOR XML PATH('')
),
1,1,'')
FROM (SELECT DISTINCT id FROM tba) AS a
)
SELECT @sql = @sql + ' UNION ALL
SELECT '+Convert(varchar(11),id)+' id,'+expr+' value'
FROM t1SET @sql = STUFF(@sql,1,12,'')
--PRINT @sql
EXEC(@sql)
id value
----------- -----------
1 600
2 200
--@sql
SELECT 1 id,10*20*3 value UNION ALL
SELECT 2 id,10*20 value
tba(Id, Adjust) AS (
SELECT 1,10 UNION ALL
SELECT 1,20 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2,10 UNION ALL
SELECT 2,20
)
SELECT Id,power(10, Sum(Log10(Adjust)))
from tba
group BY Id这种最简单