行号Tsql

Question

我有以下语法

 select rcp.CalendarPeriodId
       ,rc.CalendarId
       ,rcp.CalendarYearId
       ,rcp.PeriodNumber
       ,rcp.PeriodStartDate,rcp.PeriodEndDate
       ,CASE WHEN GETDATE() BETWEEN rcp.PeriodStartDate AND rcp.PeriodEndDate THEN 1 ELSE 0 END AS 'CurrentPeriod'
from RentCalendarPeriod rcp
LEFT JOIN RentCalendarYear rcy ON rcy.CalenderYearId = rcp.CalendarYearId
LEFT JOIN RentCalendar rc ON rc.CalendarId = rcy.CalendarId

这就是我有两个日历(CalenderID 1=每周，CalenderID 2=每月)，这是RentCalendar表。

每个Rent Calendar都有一个年份(RentCalendarYear表)，而每年又有一组期间。

​

​

您会注意到，第47行，最后一列被标记为1 (true)，这是因为它是当前周期。

我需要做的是为任何CalendarId标记前12个周期。我想知道我是否可以用ROW_NUMBER实现这一点，字段CurrentPeriod其中=1将是1，而之前的所有句号都将开始编号为2，3，4，5等等。

不过，我不知道该怎么做。

Answer 1

所以就像这样：

 SELECT * FROM (
 select rcp.CalendarPeriodId,rc.CalendarId,rcp.CalendarYearId,rcp.PeriodNumber,rcp.PeriodStartDate,rcp.PeriodEndDate,
   ROW_NUMBER() OVER(ORDER BY PeriodStartDate DESC) AS CurrentPeriod
from RentCalendarPeriod rcp
LEFT JOIN RentCalendarYear rcy ON rcy.CalenderYearId = rcp.CalendarYearId
LEFT JOIN RentCalendar rc ON rc.CalendarId = rcy.CalendarId)
WHERE currentperiod <= 12

我不确定我是否理解正确。这将在CurrentPeriod列中显示最近一周的第1周、第2周、第3周，依此类推

Answer 2

如下所示：

;WITH CTE AS (
   SELECT rcp.CalendarPeriodId, rc.CalendarId, rcp.CalendarYearId, 
          rcp.PeriodNumber, rcp.PeriodStartDate, rcp.PeriodEndDate,
          ROW_NUMBER() OVER (ORDER BY rcp.CalendarPeriodId) AS rn,
          CASE 
             WHEN GETDATE() BETWEEN rcp.PeriodStartDate AND 
                                    rcp.PeriodEndDate THEN 1 
             ELSE 0 
          END AS 'CurrentPeriod'
   FROM RentCalendarPeriod rcp
   LEFT JOIN RentCalendarYear rcy ON rcy.CalenderYearId = rcp.CalendarYearId
   LEFT JOIN RentCalendar rc ON rc.CalendarId = rcy.CalendarId
)
SELECT CalendarPeriodId, CalendarId, CalendarYearId,
       PeriodNumber, PeriodStartDate, PeriodEndDate,
       'CurrentPeriod',
       (t.rn + 1) - c.rn AS rn
FROM CTE AS c
CROSS JOIN (SELECT rn FROM CTE WHERE 'CurrentPeriod' = 1) AS t
WHERE rn BETWEEN t.rn - 11 AND t.rn

这将返回12条记录，其中包含CurrentPeriod = 1的记录和前11条记录。字段rn从具有CurrentPeriod = 1的记录开始枚举记录。