获取分隔列（月和年）的最大日期

I have this table :

我有这张桌子:

Month   Year    Provider    Number
1       2015    1           345
2       2015    1           345
3       2015    1           345
12      2015    2           444
1       2016    2           444

Let's say I want to get all different numbers by provider but only the max month and max year, something like this:

假设我想通过提供商获得所有不同的数字,但只有最大月份和最大年份,如下所示:

Month   Year    Provider    Number
3       2015    1           345
1       2016    2           444

I have this ugly query that I would like to improve :

我有这个丑陋的查询,我想改进:

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

2 个解决方案

#1

You could use a row_number and cte

你可以使用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

If you want to create a view then

如果要创建视图

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

#2

One option is to use row_number:

一种选择是使用row_number:

select *
from (
    select *, row_number() over (partition by provider 
                                 order by [year] desc, [month] desc) rn
    from dbo.Info
) t
where rn = 1

This assumes the number and provider fields are the same. If not, you may need to also partition by the number field.

这假设数字和提供者字段相同。如果没有,您可能还需要按数字字段进行分区。

#1