Mysql，左连接和计数条件

I have fallowing SQL query (pseudo query):

我已经停止了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

Query return good results. On example:

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

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

So, records in table clicks they have transactions or a few transactions or they do not.

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

Haw Can I retun COUNT() clicks who have COUNT(transactions.id) = 0 and COUNT(transactions.id) > 0 ? On example:

Haw我可以重新调用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

Tahnks for help.

Tahnks寻求帮助。

@UPDATE:

@UPDATE：

I solved my problem adding second table. Now my SQL query looks like:

我解决了添加第二个表的问题。现在我的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

2 个解决方案

#1

What about adding a second join with the clicks table

如何使用点击表添加第二个连接

JOIN (
    SELECT id 
    FROM clicks
    LEFT JOIN transactions ON clicks.key = transactions.key AND transactions.id != 0) clicks2 ON clicks.id = clicks2.id

And in the select clause use this to for the two columns

在select子句中使用this来表示两列

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions
    COUNT(clicks2.id) as clicks_with
    clicks - clicks_with AS clicks_without

#2

If I understand correctly, you can try to use CASE WHEN expression and COUNT

如果我理解正确，您可以尝试使用CASE WHEN表达式和COUNT

Because you didn' provide any sample data and expected result, so I can only provide pseudo-query.

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

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

If that didn't help you, you can provide some data and expect result, I will edit my answer.

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

#1