sql - 对比查询

Question

我正在尝试编写一个查询来比较两个日期范围内的数据。

第一个日期范围:

select code,description, quantity*each as Total
from orderiteminfo oi, orderinfo o, invoiceinfo i
where oi.orderid = o.orderid
and o.invoiceid = i.invoiceid
and i.invdate between '2014-01-01' and '2014-02-01'
group by oi.code, description, quantity, each

第二个日期范围:

select code,description, quantity*each as Total
from orderiteminfo oi, orderinfo o, invoiceinfo i
where oi.orderid = o.orderid
and o.invoiceid = i.invoiceid
and i.invdate between '2015-01-01' and '2015-02-01'
group by oi.code, description, quantity, each

我希望结果是:

代码|说明|第一个日期范围的总计|第二个日期范围的总计

Answer 1

像这样

SELECT code,
       description,
       SUM(CASE
             WHEN i.invdate BETWEEN '2014-01-01' AND '2014-02-01' THEN quantity * each
           END) AS [Total for First Date Range],
       SUM(CASE
             WHEN i.invdate BETWEEN '2015-01-01' AND '2015-02-01' THEN quantity * each
           END) AS [Total for second Date Range]
FROM   orderiteminfo oi
       INNER JOIN orderinfo o
               ON oi.orderid = o.orderid
       INNER JOIN invoiceinfo i
               ON o.invoiceid = i.invoiceid
WHERE  ( i.invdate BETWEEN '2014-01-01' AND '2014-02-01'
          OR i.invdate BETWEEN '2015-01-01' AND '2015-02-01' )
GROUP  BY oi.code,
          description 

作为旁注，始终使用 Inner Join 语法来连接两个表，而不是旧式的逗号分隔连接，并将过滤器单独放在 Where 子句中，这样更易​​读