共享联合中的行-分组依据？

Question

我有一个类似这样的查询：

SELECT * FROM (
   SELECT id, SUM( orderLineTotal ) as orderLine1, NULL as orderLine 2
   FROM Orders
   WHERE Orders.Date < @Today
   GROUP BY id

   UNION

   SELECT id, NULL as orderLine1, SUM(orderLineTotal2) as orderLine 2
   FROM Orders
   WHERE Orders.Date = @Today
   GROUP BY id
) o
GROUP BY o.id, o.orderLine1, o.orderLine2

我得到的结果如下：

 ID         OrderLine1          OrderLine2
 -----------------------------------------
 1          105.00              NULL
 1          NULL                204.00
 2          49.30               NULL
 2          NULL                94.24

有没有办法修改查询以返回类似下面这样的结果？

 ID         OrderLine1          OrderLine2
 -----------------------------------------
 1          105.00              204.00
 2          49.30                94.24

Answer 1

您希望使用一个查询和条件聚合来完成此操作：

   SELECT id,
          SUM(case when Orders.Date < @Today then orderLineTotal end) as orderLine1, 
          SUM(case when Orders.Date = @Today then orderLineTotal2 end) as orderLine2
   FROM Orders
   GROUP BY id

顺便问一下，真的有两列，orderLineTotal和orderLineTotal2吗？我怀疑只有一个，第二个sum()应该相应地改变。