从最大查询
选择列名我有一个查询是这样的:从最大查询
;WITH t as
(
select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2], 20 as [Col3], 20 as [Col4] UNION ALL
select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2], 40 as [Col3], 50 as [Col4]
)
SELECT RowNumber, ObjectID,
(
SELECT MAX(Amount)
FROM (
SELECT [Col1] AS Amount
UNION ALL
SELECT [Col2]
UNION ALL
SELECT [Col3]
UNION ALL
SELECT [Col4]
) d
WHERE Amount > 0
)
FROM t
查询工作正常,但我想知道的是马克斯(金额)的由来。 (RowNumber,ObjectId,Amount)我想要列(Col1,Col2,Col3,Col4)的名称作为字符串。
有没有办法做到这一点?
编辑 问题来自评论:如果两列有相同的最大值,它可能是一个?是的,它可以是任何一个。只要我知道它可能来自哪里,任何列名都会执行。
使用SQL Server 2008
一个步骤中,您可以使用UNPIVOT和OUTER APPLY组合:
;WITH t as (
select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2],
20 as [Col3], 20 as [Col4] UNION ALL
select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2],
40 as [Col3], 50 as [Col4])
SELECT
RowNumber,
ObjectID,
ColName,
ColAmount
FROM t
OUTER APPLY (
SELECT TOP 1
ColName,
ColAmount
FROM
(
SELECT
Col1,
Col2,
Col3,
Col4
) x
UNPIVOT (
ColAmount FOR ColName IN (Col1, Col2, Col3, Col4)
) y
WHERE ColAmount > 0
ORDER BY ColAmount DESC
) z
结果:
RowNumber ObjectID ColName ColAmount
----------- ----------- --------- -----------
1 1 Col2 20
2 2 Col4 50
谢谢,这原来是最好的解决方案。不知道UNPIVOT的功能,但这正是我所需要的。 – 2010-10-04 15:31:24
不要MAX:使用TOP避免聚合/ GROUP BY。
它还可以处理使用WITH TIES重复
我不知道,如果你遇到了什么是伪代码或者一个子查询,但这应该做你想做的
SELECT TOP 1 -- WITH TIES if needed
*
FROM
(
SELECT RowNumber, ObjectID, [Col1] AS Amount, 'Col1' AS ColName
FROM table
UNION ALL
SELECT RowNumber, ObjectID, [Col2], 'Col2' AS ColName
FROM table
UNION ALL
SELECT RowNumber, ObjectID, [Col3], 'Col3' AS ColName
FROM table
UNION ALL
SELECT RowNumber, ObjectID, [Col4], 'Col4' AS ColName
FROM table
) foo
WHERE Amount > 0
ORDER BY Amount DESC
你的主要问题是,无论你如何操作,你都必须触碰表格4次,因为子查询只返回一个值。我看不到ROW_NUMBER解决方案(但是可能有一个虽然... :-)
他的查询是每行进行的。我从来不知道你能做到这一点。 – 2010-10-01 20:16:16
@Martin Smith:它有效,但我不确定如何获取列名,因为它是只允许一个值的子查询。请参阅以下链接:http://sqlblogcasts.com/blogs/simons/archive/2006/05/08/Neat-trick-to-find-max-value-of-multiple-columns.aspx或http:// sqlblogcasts。 com/blogs/simons/archive/2006/05/16/Performance-of-MAX-trick.aspx – gbn 2010-10-01 20:20:03
是以前从未见过的。感谢您的链接! – 2010-10-01 20:22:59
这是未经测试的:但是要查看您的数据正在发生什么,这可能有所帮助。不是真正的产品代码质量:
SELECT RowNumber, ObjectID,
(
SELECT MAX(Amount)
FROM (
SELECT str([Col1]) + ", col1, " AS Amount
UNION ALL
SELECT str([Col2]) + ", col2"
UNION ALL
SELECT str([Col3]) + ", col3"
UNION ALL
SELECT str([Col4]) + ", col4"
)
WHERE Amount > 0
)
FROM table
str()是DBMS的“toString()”函数。 你的SQL看起来很奇怪,你使用的是什么DBMS?
你必须有金额>'0',但这是相当整洁+ 1 – gbn 2010-10-01 20:25:59
这是一个聪明的方式来做到这一点。没有考虑将列名添加为像这样的字符串。谢谢 – 2010-10-01 20:34:52
;WITH t1 as(
select 1 as RowNumber, 1 as ObjectID, 10 as [Col1], 20 as [Col2], 20 as [Col3], 20 as [Col4] UNION ALL
select 2 as RowNumber, 2 as ObjectID, 20 as [Col1], 30 as [Col2], 40 as [Col3], 50000045 as [Col4]
),
t2 as(
SELECT RowNumber, ObjectID,
(
SELECT TOP 1 CAST(C AS BINARY(4)) + CAST(Amount as BINARY(4))
FROM (
SELECT 'Col1' AS C, [Col1] AS Amount
UNION ALL
SELECT 'Col2' AS C, [Col2]
UNION ALL
SELECT 'Col3' AS C, [Col3]
UNION ALL
SELECT 'Col4' AS C, [Col4]
) d
WHERE Amount > 0
ORDER BY Amount desc
) AS Top1
FROM t1
)
SELECT RowNumber,
ObjectID,
CAST(Left(Top1, 4) AS CHAR(4)) AS Col,
CAST(SUBSTRING(Top1,5,4) AS INT) AS Amount
FROM t2
谢谢,这也相当干净 – 2010-10-02 00:48:44
甚至整洁的字符串concat:固定长度的“编码” – gbn 2010-10-03 09:21:17
您正在使用什么RDBMS? – 2010-10-01 20:03:33
如果Col1和Col2具有相同的MAX,该怎么办?它可能是一个......? – gbn 2010-10-01 20:05:40
希望你不介意我刚刚编辑你的代码,使它成为一个可运行的例子,因为我从来没有见过这种方法。随意将它滚回去! – 2010-10-01 20:20:58