我在SQL Server 2008中有一个表格，格式如下（简化格式）：
日期里程成本
我想得到像一样的结果
上周本周差异Miles成本
我怎样才能做到这一点？我需要pivot/unpivot吗？或者实现这一点最简单、性能有效的方法是什么。谢谢
SELECT 'Miles' AS Type, SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) as lastweek
, SUM(CASE WHEN date = thisweek THEN miles ELSE 0 END) as thisweek
SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) - SUM(CASE WHEN date = thisweek THEN miles ELSE 0 END) as variance
FROM yourtable
UNION
SELECT 'Cost' AS Type, SUM(CASE WHEN date = lastweek THEN cost ELSE 0 END) as lastweek
, SUM(CASE WHEN date = thisweek THEN cost ELSE 0 END) as thisweek
SUM(CASE WHEN date = lastweek THEN cost ELSE 0 END) - SUM(CASE WHEN date = thisweek THEN cost ELSE 0 END) as variance
FROM yourtable
这不是100%正确的，但语法应该是正确的，应该会让你非常接近你想要的