计算每个特定行的事务总数，即使0

Question

我有三栏，即：

1. Payment Channel
2. Payment Name
3. Total Transaction

Total Transaction将是每个Payment Channel和Payment Name的计数。

在Payment Channel中，值具有PC01-PC09.

我希望我的结果显示所有的Payment Channel，即使没有事务。

这里是我的SQL脚本：

SELECT
    B2C_BUY_LOG.PG_CHANNEL AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM B2C_BUY_LOG
INNER JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER JOIN COMM_CODE ON B2C_BUY_LOG.PG_CHANNEL = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
AND COMM_CODE.CODE IN('PC01', 'PC02', 'PC03', 'PC04', 'PC05', 'PC06', 'PC07', 'PC08', 'PC09')
GROUP BY Payment_Channel, Payment_Name;

这里是我的查询结果：

Payment_Channel   Payment_Name    Total_Transaction
PC01              Name-1          14
PC02              Name-2          2
PC03              Name-3          7
PC04              Name-4          9
PC06              Name-6          21
PC08              Name-8          18
PC09              Name-9          95

该查询只返回连接表之间的匹配值，因为它是内部连接，而且由于没有事务，结果中缺少PC05和PC07。我也尝试过不同的连接。

如果没有事务，如何显示计数为0的PC05和PC07？

谢谢!

Answer 1

SELECT
    channels.channel AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM (           SELECT 'PC01' channel 
       UNION ALL SELECT 'PC02' 
       UNION ALL SELECT 'PC03' 
       UNION ALL SELECT 'PC04' 
       UNION ALL SELECT 'PC05' 
       UNION ALL SELECT 'PC06' 
       UNION ALL SELECT 'PC07' 
       UNION ALL SELECT 'PC08' 
       UNION ALL SELECT 'PC09') channels
LEFT JOIN B2C_BUY_LOG ON B2C_BUY_LOG.PG_CHANNEL = channels.channel
LEFT /* or INNER */ JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER /* or LEFT */ JOIN COMM_CODE ON B2C_channels.channel = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
GROUP BY Payment_Channel, Payment_Name;