sql server获取近两年每周下单量TOP20的城市

业务需求：

• 近两年按周统计每周每个城市的下单量
• 根据订单量统计每周下单量TOP20的城市
• 每周四作为一周的第一天

实现SQL如下：

SET DATEFIRST 3
select * from 
(select *,
ROW_NUMBER() over(partition by 年份,weekCount order by orderscount desc) rowNum 
from (select  Year(dd.StartDate) as 年份,dd.weekCount,
pu_city_airline,sum(orderscount) orderscount 
from Repl_DJ_PerDay_Summary(nolock) ssum
left join (SELECT DISTINCT
	   weekCount,
       StartDate,
       CONVERT(   CHAR(10),
                  CASE
                      WHEN ff.weekCount = 53 THEN
                          DATEADD(DAY, 7, ff.StartDate)
                      ELSE
                          ff.EndDate
                  END,
                  120
              ) EndDate,
       CONVERT(