mysql - 如果在其他列上查询，则获取用计数填充的计数总和 - 到右侧的最后一列

Question

我通过以下查询得到了这个结果

            SELECT
                owner.country AS country,COUNT(main.accounts) as "total_accounts",COUNT(IF(main.status = "active_accounts",1,NULL)) as "status",COUNT(IF(main.total_toys = 10,1,NULL)) as "10ToyOwners"
            FROM accounts AS main
            JOIN (
                SELECT MAX(hist.dyna_id) AS maxid,
                    hist.LastEditor AS lasteditor
                FROM history_gt AS hist
                    WHERE 
                        hist.LastEditor != "abc@example.com"
                    GROUP BY 
                        hist.dyna_id
                )AS history 
                    ON 
                        main.id = history.maxid
            LEFT JOIN
                all_users AS owner
            ON
                history.lasteditor = owner.primary_email 
            GROUP BY 
                country
            ORDER BY 
            main.ticket_number

我正在寻找的是获取填充在“b、c、d”列上的“total_accounts - active_accounts + 10ToyOwner”的总和，如果查询则计数 - 到右侧的最后一列(下面的屏幕截图)

                COUNT(IF(main.status = 'active_accounts', 1, NULL)) AS status,
                COUNT(IF(main.total_toys = 10, 1, NULL)) AS tenToyOwners,

-- 下面的新术语

status + tenToyOwner  AS desired_total <-- this line doenst work

Answer 1

据我所知，您只需要在当前的 select 子句中添加另一个术语:

SELECT
    owner.country AS country,
    COUNT(main.accounts) AS total_accounts,
    COUNT(IF(main.status = 'active_accounts', 1, NULL)) AS status,
    COUNT(IF(main.total_toys = 10, 1, NULL)) AS 10ToyOwners,
    -- new term below
    COUNT(main.accounts) - COUNT(IF(main.status = 'active_accounts', 1, NULL)) +
        COUNT(IF(main.total_toys = 10, 1, NULL)) AS desired_total
FROM accounts AS main
INNER JOIN ( ...

为了简洁起见，我省略了您的其余查询。请注意，我们不能使用别名来生成新术语，因为别名不能由定义它的同一 selectnin 使用。我们可以子查询您当前的查询并使用别名，但我认为没有必要这样做。