EmpNo部门销售1 10 92 20 71 10 101 10 53 10 33 10 81 10 114 30 54 30 5
EmpNo部门销售额增长1 10 5 51 10 9 41 10 10 11 10 11 13 10 33 10 8 52 20 7 74 30 5 54 30 6 1
您可以使用LAG
来获取按员工划分的最后一个值,但您需要一个额外的列(如id或日期(来对列进行排序。这是一个简单的示例,只包含示例的前几条记录(添加一个id
列(:
SELECT *,
Sales - LAG(Sales, 1, 0) OVER (PARTITION BY EmpNo ORDER BY Id) AS Growth
FROM SalesData
带有样本数据
;WITH SalesData (Id, EmpNo, Dept, Sales) AS (
SELECT 1, 1, 10, 9
UNION ALL SELECT 2, 1,10, 10
UNION ALL SELECT 3, 2, 10, 7
)
SELECT *,
Sales - LAG(Sales, 1, 0) OVER (PARTITION BY EmpNo ORDER BY Id) AS Growth
FROM SalesData
此示例生成:
Id EmpNo Dept Sales Growth
----------- ----------- ----------- ----------- -----------
1 1 10 9 9
2 1 10 10 1
3 2 10 7 7