SQL查询以获取当前和去年的销售额

Question

我有下表销售:

Date        Store   Sales
1/1/2015    St01    12123
1/1/2015    St02    3123
1/1/2016    St01    4213
1/1/2016    St03    2134

当我尝试自行加入以获得今年和去年的销售额时，关闭的商店没有出现。结果应该是这样的:

Date        Store   This year Sales   Last Year Sales
1/1/2016    St01    4213              1212
1/1/2016    St02    0                 3123
1/1/2016    St03    2134              0

我的查询如下:

SELECT CY.DATE, 
       CY.store cy.Sales, 
       LY.sales 
FROM   sales CY, 
       sales LY 
WHERE  CY.store(+) = LY.store(+) 
       AND LY.DATE = CY.DATE - 365 

Answer 1

Oracle 设置:

CREATE TABLE sales ( "DATE", Store, Sales ) AS
SELECT DATE '2015-01-01', 'St01', 12123 FROM DUAL UNION ALL
SELECT DATE '2015-01-01', 'St02',  3123 FROM DUAL UNION ALL
SELECT DATE '2016-01-01', 'St01',  4213 FROM DUAL UNION ALL
SELECT DATE '2016-01-01', 'St03',  2134 FROM DUAL;

查询:

SELECT TRUNC( SYSDATE, 'YY' ) AS "DATE",
       Store,
       SUM( CASE WHEN "DATE" = TRUNC( SYSDATE, 'YY' )
                 THEN sales END )
         AS "This year sales",
       SUM( CASE WHEN "DATE" = ADD_MONTHS( TRUNC( SYSDATE, 'YY' ), -12 )
                 THEN sales END )
         AS "Last year sales"
FROM   sales
GROUP BY store
ORDER BY store;

输出:

DATE                STORE This year sales Last year sales
------------------- ----- --------------- ---------------
2016-01-01 00:00:00 St01             4213           12123 
2016-01-01 00:00:00 St02                             3123 
2016-01-01 00:00:00 St03             2134