sql - 在 SQL Server 上对数据进行分组

Question

我在 SQL Server 中有这个表:

Year    Month       Quantity
----------------------------
2015    January     10
2015    February    20
2015    March       30
2014    November    40
2014    August      50

如何识别不同的年份和月份，添加另外两列，将相同的年份与数字分组，然后按顺序将不同的月份分组，如示例所示

Year    Month       Quantity    Group   Subgroup
------------------------------------------------
2015    January     10          1       1
2015    February    20          1       2
2015    March       30          1       3
2014    November    40          2       1
2014    August      50          2       2

Answer 1

您可以使用DENSE_RANK为您计算组:

SELECT t1.*, DENSE_RANK() OVER (ORDER BY Year DESC) AS [Group],
  DENSE_RANK() OVER (PARTITION BY Year ORDER BY DATEPART(month, Month + ' 01 2010')) AS [SubGroup]
FROM t1
ORDER BY 4, 5

查看此fiddle .