我在报表设计器中有这样的表格：
Category: 1 2 3 4 Total Max Amount: x y z c ?
我需要得到Max Amount的总和,但是表达式不允许我取Sum(Max(amount)),并且对此单元格禁用add total.
最大金额行是一个表达式,它取每个类别的最大值.源数据有重复的值,所以我只取最大值.例如：
Category Amount
1 4.6
1 4.6
1 4.6
2 5
3 4
表中的其他列是不同的,但数量将是相同的,所以我不仅可以选择不同的值.
解决方法
也许是这样的：
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：
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个类别.然后动态支点将是最佳解决方案.像这样：
测试数据
CREATE TABLE #T
(
Category INT,Amout FLOAT
)
INSERT INTO #T
VALUES
(1,4.6),(1,(2,5),(3,4)
唯一的列名称
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
动态枢轴
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)
