SQL子选择如果存在

Question

我正在使用 SQL Server 2012。我有两个表来保存产品订单。具有接收日期的订单和具有价格和订单 ID fk 的 OrderItem。

我正在尝试编写一个查询，获取某个日期范围内的所有订单，按日期对它们进行分组，然后将所有订单商品的价格相加，以获得该日期所有订单的总和。

我有这个工作。现在我想添加另一列来选择当天总价与 7 天前总价之间的差异。如果 7 天前没有订单，则该列应为空。

所以目前我有以下查询:

select cast(o.ReceivedDate as date) as OrderDate, 
coalesce(count(orderItems.orderId), 0) as Orders,
coalesce(sum(orderItems.Price), 0) as Price
from [Order] o

left outer join (
        select o.Id as orderId, sum(ot.Price) as Price
        from OrderItem ot
        join [Order] o on ot.OrderId = o.Id
        where o.ReceivedDate >= @DateFrom and o.ReceivedDate <= @DateTo
        group by o.Id
) as orderItems on o.Id = orderItems.orderId

where o.ReceivedDate >= @DateFrom and o.ReceivedDate <= @DateTo
group by cast(o.ReceivedDate as date)
order by cast(o.ReceivedDate as date) desc

那么如何将其他列添加到此查询中？我需要做类似的事情:

//pseudo 
if o.RecievedDate - 7 exists then orderItems.Price - Price from 7 days ago else null

但我不知道该怎么做？我创建了一个 sqlfiddle 来帮助解释 http://sqlfiddle.com/#!6/8b837/1

因此，根据我的示例数据，我想要实现的结果是这样的:

|  ORDERDATE | ORDERS | PRICE |  DIFF7DAYS  |
---------------------------------------------
| 2013-01-25 |      3 |    38 |          28 |
| 2013-01-24 |      1 |    12 |        null |
| 2013-01-23 |      1 |    10 |        null |
| 2013-01-22 |      1 |    33 |        null |
| 2013-01-18 |      1 |    10 |        null |
| 2013-01-10 |      1 |     3 |         -43 |
| 2013-01-08 |      2 |    11 |        null |
| 2013-01-04 |      1 |     1 |        null |
| 2013-01-03 |      3 |    46 |        null |

如您所见，25 号有 7 天前的订单，因此显示了差异。 24 号不是，所以显示 null。

任何帮助将不胜感激。

Answer 1

不确定为什么在[Orders]表和子查询之间使用左外连接，因为不能有没有订单项的订单(一般情况):

要获得结果，您可以使用 CTE 以简化版本执行此操作，如下所示

SQL-FIDDLE-DEMO

;with cte as (
  select convert(date,o.ReceivedDate) orderDate,
       count(distinct o.Id) as Orders,
       coalesce(sum(ot.Price),0) as Price
  from OrderItem ot
        join [Order] o on ot.OrderId = o.Id
  where o.ReceivedDate >= @DateFrom and o.ReceivedDate <= @DateTo
  group by convert(date,o.ReceivedDate)
)
select c1.orderDate, c1.Orders, c1.Price, c1.Price-c2.Price DIFF7DAYS
from cte c1 left join cte c2 on dateadd(day,-7,c1.orderdate) = c2.orderdate
order by c1.orderdate desc


|  ORDERDATE | ORDERS | PRICE | DIFF7DAYS |
-------------------------------------------
| 2013-01-25 |      3 |    38 |        28 |
| 2013-01-24 |      1 |    12 |    (null) |
| 2013-01-23 |      1 |    10 |    (null) |
| 2013-01-22 |      1 |    33 |    (null) |
| 2013-01-18 |      1 |    10 |    (null) |
| 2013-01-10 |      1 |     3 |       -43 |
| 2013-01-08 |      2 |    11 |    (null) |
| 2013-01-04 |      1 |     1 |    (null) |
| 2013-01-03 |      3 |    46 |    (null) |