MYSQL 对每种可能的情况进行计数和求和

Question

我有这样的 mysql 查询....

    "SELECT
SUM(CASE WHEN ticket_source='bpt' THEN tix_tickets.ticket_price END) AS bpt_money,
COUNT(CASE WHEN ticket_source='bpt' THEN 1 END) AS bpt_num,
SUM(CASE WHEN ticket_source='door' THEN tix_tickets.ticket_price END) AS door_money,
COUNT(CASE WHEN ticket_source='door' THEN 1 END) AS door_num,
SUM(CASE WHEN ticket_source='goldstar' THEN tix_tickets.ticket_price END) AS goldstar_money,
COUNT(CASE WHEN ticket_source='goldstar' THEN 1 END) AS goldstar_num,
COUNT(CASE WHEN ticket_price='0' THEN 1 END) AS free_tix,
COUNT(CASE WHEN ticket_price='10' THEN 1 END) AS ten_buck_tix,
COUNT(CASE WHEN ticket_price='20' THEN 1 END) AS twenty_buck_tix,
COUNT(CASE WHEN ticket_price='25' THEN 1 END) AS twentyfive_buck_tix
FROM tix_people,tix_tickets WHERE tix_people.ID=tix_tickets.holder_id GROUP BY ticket_date ORDER BY ticket_date"

它工作得很好，但我想使用表中的每个 ticket_price 而无需单独调用它们。我想获取每个 ticket_source 而无需单独调用它们。我认为随着来源数量和价格的增加，我现在可以缩短代码并使其变得更好。

Answer 1

您必须写出您要选择的列。如果你想尝试构建一个查询，你可以这样做。这是您选择的一些重构。我清理了一些 case 语句以仅使用 bool 求和

SELECT
    SUM(CASE WHEN ticket_source='bpt' THEN tix_tickets.ticket_price END) AS bpt_money,
    SUM(ticket_source='bpt') AS bpt_num,
    SUM(CASE WHEN ticket_source='door' THEN tix_tickets.ticket_price END) AS door_money,
    SUM(ticket_source='door') AS door_num,
    SUM(CASE WHEN ticket_source='goldstar' THEN tix_tickets.ticket_price END) AS goldstar_money,
    SUM(ticket_source='goldstar') AS goldstar_num,
    SUM(ticket_price='0') AS free_tix,
    SUM(ticket_price='10') AS ten_buck_tix,
    SUM(ticket_price='20') AS twenty_buck_tix,
    SUM(ticket_price='25') AS twentyfive_buck_tix
FROM tix_people
JOIN tix_tickets on tix_people.ID=tix_tickets.holder_id 
GROUP BY ticket_date 
ORDER BY ticket_date