我在报表设计器中有这样的表格：
@H_502_2@Category: 1 2 3 4 Total
Max Amount: x y z c ?
我需要得到Max Amount的总和,但是表达式不允许我取Sum(Max(amount)),并且对此单元格禁用add total.
最大金额行是一个表达式,它取每个类别的最大值.源数据有重复的值,所以我只取最大值.例如：
@H_502_2@Category Amount 1 4.6 1 4.6 1 4.6 2 5 3 4表中的其他列是不同的,但数量将是相同的,所以我不仅可以选择不同的值.
解决方法
也许是这样的：
@H_502_2@SELECT
SUM(t1.maxAmout)
FROM
(
SELECT
MAX(t.Amout) AS maxAmout,t.Category
FROM
yourTable AS t
GROUP BY
t.Category
) AS t1
你也可以这样做.如果您使用的是sql server 2005：
@H_502_2@SELECT pvt.[1],pvt.[2],pvt.[3],( pvt.[1]+ pvt.[2]+ pvt.[3] ) AS Total FROM ( SELECT t.Category,t.Amout FROM yourTable AS t ) AS SourceTable PIVOT ( MAX(Amout) FOR Category IN([1],[2],[3]) ) AS pvt编辑
如果您有1000个类别.然后动态支点将是最佳解决方案.像这样：
测试数据
@H_502_2@CREATE TABLE #T ( Category INT,Amout FLOAT ) INSERT INTO #T VALUES (1,4.6),(1,(2,5),(3,4)唯一的列名称
@H_502_2@DECLARE @cols VARCHAR(MAX) DECLARE @colsTotal VARCHAR(MAX) ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(PARTITION BY t.Category ORDER BY t.Amout) AS RowNbr,t.* FROM #T AS t ) SELECT @cols = COALESCE(@cols + ','+QUOTENAME(Category),QUOTENAME(Category)),@colsTotal=COALESCE(@colsTotal + '+ISNULL('+QUOTENAME(Category)+',0)','ISNULL('+QUOTENAME(Category)+',0)') FROM CTE WHERE CTE.RowNbr=1 ORDER BY Category动态枢轴
@H_502_2@DECLARE @query NVARCHAR(4000)= N'SELECT '+@cols+',( '+@colsTotal+' ) AS Total FROM ( SELECT t.Category,t.Amout FROM #T AS t ) AS SourceTable PIVOT ( MAX(Amout) FOR Category IN('+@cols+') ) AS pvt' EXECUTE(@query)