我自己也曾经遇到过这个问题,解决方法是要用不同的思路来考虑你的查询。
我的目标是获取每个DocGroupViewID行中Del_Index值最高(即max)的那一行:
SELECT
T.DocGroupViewID,
T.Del_Index,
T.ID
FROM MyTable T
WHERE T.Del_Index = (
SELECT MAX( T1.Del_Index ) FROM MyTable T1
WHERE T1.DocGroupViewID = T.DocGroupViewID
)
当多行具有相同的Del_Index
时,情况会变得更加复杂,因为您需要某种方式来选择要显示哪一行。
编辑:想跟进另一个选项
您可以使用RANK()
或ROW_NUMBER()
函数与CTE一起使用,以获得对结果更多的控制,如下所示:
DECLARE @t TABLE (
ID int IDENTITY(1,1) PRIMARY KEY,
Del_Index int,
DocGroupViewID int
)
INSERT INTO @t
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 3
SELECT * FROM @t
;WITH cteRank AS
(
SELECT
DocGroupViewID,
Del_Index,
ID,
RANK() OVER
(PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
AS RowRank,
ROW_NUMBER() OVER
(PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
AS RowNumber
FROM @t
)
SELECT *
FROM cteRank
WHERE RowRank = 1
;WITH cteRowNumber AS
(
SELECT
DocGroupViewID,
Del_Index,
ID,
RANK() OVER
(PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
AS RowRank,
ROW_NUMBER() OVER
(PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
AS RowNumber
FROM @t
)
SELECT *
FROM cteRowNumber
WHERE RowNumber = 1
如果您有解决方案来排序,只需将其添加到 ORDER BY
中即可。