问题描述
我希望有一种不使用子查询的简单方法:
I'm hoping there's a simple way to do this without using a sub-query:
场景:您有TableA"，其中包含Key"、SubKey"和Value"列.我需要获取给定密钥"的 MAX(子密钥")的值".
Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key".
所以如果表包含行:
KEY SUBKEY VALUE
1 1 100
1 2 200
1 3 300
对于 Key = 1，我需要值 300.我希望做这样的事情:
For Key = 1, I need the value 300. I was hoping to do something like this:
SELECT
VALUE
FROM
TableA
WHERE
Key = 1
HAVING
SubKey = MAX(SubKey)
但那是不行的.有没有办法在不做WHERE SubKey = (subselect for max subkey)"的情况下做到这一点?
But that's a no-go. Is there a way to do this without doing a 'WHERE SubKey = (subselect for max subkey)'?
推荐答案
使用自联接:
这将返回所有子键值匹配的值，以防有多个.
Using a self join:
This will return all the values with subkey values that match, in case there are multiples.
SELECT a.value
FROM TABLE a
JOIN (SELECT MAX(t.subkey) AS max_subkey
FROM TABLE t
WHERE t.key = 1) b ON b.max_subkey = a.subkey
WHERE a.key = 1
使用 RANK &CTE(SQL Server 2005+):
这将返回所有子键值匹配的值，以防有多个.
Using RANK & CTE (SQL Server 2005+):
This will return all the values with subkey values that match, in case there are multiples.
WITH summary AS (
SELECT t.*,
RANK() OVER(ORDER BY t.subkey DESC) AS rank
FROM TABLE t
WHERE t.key = 1)
SELECT s.value
FROM summary s
WHERE s.rank = 1
使用 ROW_NUMBER &CTE(SQL Server 2005+):
这将返回一行，即使有多个具有相同子键值...
Using ROW_NUMBER & CTE (SQL Server 2005+):
This will return one row, even if there are more than one with the same subkey value...
WITH summary AS (
SELECT t.*,
ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
FROM TABLE t
WHERE t.key = 1)
SELECT s.value
FROM summary s
WHERE s.rank = 1
使用顶部:
这将返回一行，即使有多个具有相同子键值...
Using TOP:
This will return one row, even if there are more than one with the same subkey value...
SELECT TOP 1
t.value
FROM TABLE t
WHERE t.key = 1
ORDER BY t.subkey DESC
这篇关于T-SQL:基于MAX(其他列)选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！