tsql:over子句

在这种情况下:

SELECT ROW_NUMBER() over(Partition By Year(OrderDate)
 order by OrderDate) as RowN,*
  FROM [Northwind].[dbo].[Orders]
  order by RowN

你看到的是,它给你一个按年份划分的行数,意思是 每年都有自己的爬山排数 .要使结果更加清晰,请执行以下操作:

SELECT ROW_NUMBER() over(Partition By Year(OrderDate)
 order by OrderDate) as RowN,*
  FROM [Northwind].[dbo].[Orders]
  order by RowN, Year(OrderDate)

这意味着,比如说1997年,每年都会有1至N号订单在当年的日期前订购……就像1997年的1号订单、1997年的2号订单等。

如果你这样做,结果会更有意义:

SELECT
    Year(OrderDate),
    ROW_NUMBER() over(Partition By Year(OrderDate)order by OrderDate) as RowN,
    *
FROM [Northwind].[dbo].[Orders]
ORDER BY Year(OrderDate), RowN

现在您可以看到,每年都有从1开始的行数增加,按订单日期排序:

Year RowN Order Date
1997 1    10400 1997-01-01 00:00:00
1997 2    10401 1997-01-01 00:00:00
1997 3    10402 1997-01-02 00:00:00
...
1998 1    10808 1998-01-01 00:00:00
1998 2    10809 1998-01-01 00:00:00
1998 3    10810 1998-01-01 00:00:00
...