sql server 怎么实现 迭乘

[jldzy]

现在有一张表tba(Id int, Adjust int)
数据：
1,10
1,20
1,3，
2,10
2,20

等到结果
-- 10*20*3
1 600
-- 10*20
2 200

[还在加载中灬]

;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

没有累乘，可以考虑用递归

[Tiger_Zhao]

DECLARE @sql varchar(max)
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 t1

SET @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

[jldzy]

;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 ) SELECT Id,power(10, Sum(Log10(Adjust))) from tba group BY Id 这种最简单

[还在加载中灬]

引用 3 楼 jldzy 的回复:

;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 ) SELECT Id,power(10, Sum(Log10(Adjust))) from tba group BY Id 这种最简单

涨姿势了...

[向东流]

这样做是可以，但是不是很稳定。第一个1,10改成1，101结果就不对了，用自然对数可能会好点，但是还是不建议这样求结果集乘积。