使用的sql如下:
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;
生成的效果如下:
这个已经实现。
那么现在我想在最前边加一个列groupNumber,这个列根据TerritoryID 来显示,也就是TerritoryID为Null的,groupNumber就为1;TerritoryID为1的,groupNumber就为2...最后的效果图如下:
这个如何实现呢?在线等!!!