SQL Server 2008报表:
问题描述:
组
的最大的总和我有一个这样的表在报表设计:SQL Server 2008报表:
Category: 1 2 3 4 Total
Max Amount: x y z c ?
我需要得到的总最大金额,但表现不会让我拿点心(最大(金额)),并且该单元格的添加总计被禁用。
最大金额行是一个表达式,它采用每个类别的最大值。源数据具有重复值,所以我只取最大值。例如:
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,4.6),
(1,4.6),
(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
'[email protected]+',
(
'[email protected]+'
) AS Total
FROM
(
SELECT
t.Category,
t.Amout
FROM
#T AS t
) AS SourceTable
PIVOT
(
MAX(Amout)
FOR Category IN('[email protected]+')
) AS pvt'
EXECUTE(@query)
答
WITH
aggregate
AS
(
SELECT
category,
MAX(amount) AS max_amount
FROM
yourTable
GROUP BY
category
)
SELECT
MAX(CASE WHEN category = 1 THEN max_amount ELSE NULL END) AS [1],
MAX(CASE WHEN category = 2 THEN max_amount ELSE NULL END) AS [2],
MAX(CASE WHEN category = 3 THEN max_amount ELSE NULL END) AS [3],
MAX(CASE WHEN category = 4 THEN max_amount ELSE NULL END) AS [4],
SUM(max_amount) AS [total]
FROM
aggregate
答
下返回一个值:
SELECT DISTINCT SUM(MAX(Amount)) OVER()
FROM atable
GROUP BY Category
你可以使用它作为一个子查询计算Total
列的值。
另外,TOP (1)
可以用来代替的DISTINCT
(不知道为什么我以前无法想象的吧):
SELECT TOP (1) SUM(MAX(Amount)) OVER()
FROM atable
GROUP BY Category
什么版本的SQL您使用的? – Arion 2012-04-19 14:35:38
我正在使用2008 – richsoni 2012-04-19 16:09:37