Mysql，左连接和计数条件

Question

我有休闲的SQL查询(伪查询):

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions 
FROM 
    campaign
LEFT JOIN   
    clicks ON clicks.key = campaign.key
LEFT JOIN (
    SELECT 
        id, key 
    FROM 
        transactions 
    GROUP BY 
        userkey 
    ) transactions ON clicks.key = transactions.key;
GROUP BY
    campaign.id

查询返回良好的结果。例如:

column | columns [..] | 34 | 10
column | columns [..] | 22 | 1
column | columns [..] | 34 | 17

因此，在表格点击中记录他们有交易或一些交易或没有交易。

我可以重新调整 COUNT() 点击 COUNT(transactions.id) = 0 和 COUNT(transactions.id) > 0 吗？例如:

column | columns [..] | 34 | 10 | 4 (count data from clicks table which have related transactions) | 30 (count data from clicks table which not have related transactions)
column | columns [..] | 22 | 1  | 6  | 16
column | columns [..] | 34 | 17 | 10 | 24

谢谢帮助。

@更新:

我通过添加第二张表解决了我的问题。现在我的 SQL 查询看起来像:

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions,
    COUNT(clicks_count.id) as witchout_transactions, 
    (COUNT(clicks.id) - COUNT(clicks_count.id)) as witch_transactions 
FROM 
    campaign
LEFT JOIN   
    clicks ON clicks.key = campaign.key
LEFT JOIN (
    SELECT 
        id, key 
    FROM 
        transactions 
    GROUP BY 
        userkey 
    ) transactions ON clicks.key = transactions.key
LEFT JOIN (
    SELECT 
        clicks.id, 
        COUNT(transactions.id) AS transactions 
    FROM 
        clicks 
    LEFT JOIN transactions ON clicks.key = transactions.key 
    GROUP BY clicks.id 
    HAVING transactions = 0
    ) clicks_count ON clicks_count.id = clicks.id 
GROUP BY
    campaign.id

Answer 1

如果我没理解错，你可以尝试使用CASE WHEN表达式和COUNT

因为你没有提供任何示例数据和预期结果，所以我只能提供伪查询。

SELECT...,
   COUNT(CASE WHEN [have transactions condition] then 1 end),
   COUNT(CASE WHEN [not have related transactions condition] then 1 end)

如果这对您没有帮助，您可以提供一些数据并期待结果，我会编辑我的答案。