sql - 毫秒访问查询枢轴

Question

我有两张桌子。

tblA
ID   Date      Price
--------------------
1    10/1/18   100
2    10/2/18   200
3    10/1/19    50

tblB
ID   Date      Price
------------------
1    10/5/18   100
2    10/6/18   100
3    10/7/18   100

我有以下查询来总结每月的价格。

TRANSFORM IIf(Sum([SumPrice]) Is Null,0,Sum([SumPrice])) AS PricePerMonth
SELECT Format([AllDate],"yyyy") AS [Year], t.Name
FROM (
    SELECT [Date] as AllDate, Nz(Price, 0) AS SumPrice, 'tblA' As Name
    FROM tblA
UNION All
    SELECT [Date] as AllDate, Nz(Price, 0) AS SumPrice, 'tblB' As Name
    FROM tblB
)  AS t
GROUP BY Format([AllDate],"yyyy"), t.Name
ORDER BY Format([AllDate],"yyyy") DESC , t.Name
PIVOT Month([AllDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

输出查询：

Year  Name     1    2    3    4    5    6    7    8    9    10    11    12
--------------------------------------------------------------------------
2019  tblA    50    0    0    0    0    0    0    0    0     0     0     0
2018  tblA   100  200    0    0    0    0    0    0    0     0     0     0
2018  tblB     0    0    0    0  100  100  100    0    0     0     0     0

有没有办法得到这个输出：

Year  Name     1    2    3    4    5    6    7    8    9    10    11    12
--------------------------------------------------------------------------
2019  tblA    50    0    0    0    0    0    0    0    0     0     0     0
2019  tblB     0    0    0    0    0    0    0    0    0     0     0     0   <===Different from above.
2018  tblA   100  200    0    0    0    0    0    0    0     0     0     0
2018  tblB     0    0    0    0  100  100  100    0    0     0     0     0

如您所见，tblB 没有 2019 年的数据。所以我需要想办法添加它。

谢谢你。

标签： sqlms-access

Answer 1

在 Union All 中添加了额外的 Select 以获取另一个表中缺少的日期。

    TRANSFORM IIf(Sum([SumPrice]) Is Null,0,Sum([SumPrice])) AS PricePerMonth
SELECT Format([AllDate],"yyyy") AS [Year], t.Name
FROM (
       SELECT [Date] as AllDate, Nz(Price, 0) AS SumPrice, 'tblA' As Name
    FROM tblA
UNION All
    SELECT [Date] as AllDate, Nz(Price, 0) AS SumPrice, 'tblB' As Name
    FROM tblB

Union All

SELECT  tblb.Date as dt, 0 ,  'tblA' As Name
FROM tblB Left  JOIN tblA ON  Format(tblA.[Date],'yyyy')  = Format(tblB.[Date],'yyyy') 
where not Format(tblA.Date,'yyyy') = Format(tblb.Date,'yyyy')

Union All

SELECT (tblA.Date) as dt, 0 ,  'tblB' As Name
FROM tblA Left  JOIN tblB ON  Format(tblA.[Date],'yyyy')  = Format(tblB.[Date],'yyyy') 
where not Format(tblA.Date,'yyyy') = Format(tblb.Date,'yyyy')

)  AS t
GROUP BY  t.Name,Format([AllDate],"yyyy")
ORDER BY Format([AllDate],"yyyy") DESC , t.Name
PIVOT Month([AllDate]) In (1,2,3,4,5,6,7,8,9,10,11,12)

使用@YvetteLee 查询，结果如下所示（添加屏幕截图只是为了显示差异）