我有这张桌子：
Month Year Provider Number
1 2015 1 345
2 2015 1 345
3 2015 1 345
12 2015 2 444
1 2016 2 444
假设我想按提供商获取所有不同的数字，但只能获取最大月份和最大年份，如下所示：
Month Year Provider Number
3 2015 1 345
1 2016 2 444
我有一个我想改进的丑陋查询：
SELECT (SELECT max([Month])
FROM dbo.Info b
WHERE b.Provider = a.Provider
AND b.Number = a.Number
AND [Year] = (SELECT max([Year])
FROM dbo.Info c
WHERE c.Provider = a.Provider
AND c.Number = a.Number)) AS [Month],
(SELECT max([Year])
FROM dbo.Info d
WHERE d.Provider = a.Provider
AND d.Number = a.Number)) AS [Year],
a.Provider,
a.Number
FROM dbo.Info a
您可以使用row_number和cte
;WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Provider ORDER BY [Year] DESC, [Month] DESC) as rNum
FROM Info)
SELECT *
FROM cte where rNum = 1
如果要创建视图，则
CREATE VIEW SomeViewName
AS
WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Provider ORDER BY [Year] DESC, [Month] DESC) as rNum
FROM Info)
SELECT *
FROM cte where rNum = 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句