mysql - SQL 嵌套带计数的子查询

Question

有没有更方便、更简单的方法来编写下面的 SQL 代码？

SELECT
              products.name,
              (SELECT COUNT(*) FROM `sales` WHERE sales.product_id = products.id AND sales.payment_method = 'debt') AS debt, 
              (SELECT COUNT(*) FROM `sales` WHERE sales.product_id = products.id AND sales.payment_method = 'cash') AS cash,
              (SELECT COUNT(*) FROM `sales` WHERE sales.product_id = products.id AND sales.payment_method = 'free') AS free

            FROM `sales`, `products`
            WHERE `sales`.`product_id` = `products`.`id`
            GROUP BY `products`.`id`

Answer 1

您可以根据Payment_method 进行条件SUM() 聚合:

Select  P.name,
        Sum(Case When S.Payment_method = 'debt' Then 1 Else 0 End) As Debt,
        Sum(Case When S.Payment_method = 'cash' Then 1 Else 0 End) As Cash,
        Sum(Case When S.Payment_method = 'free' Then 1 Else 0 End) As Free
From    Sales       S
Join    Products    P   On  S.Product_id = P.Id
Group By P.Name

我还添加了别名以使其更具可读性，并将 GROUP BY 更改为 P.Name 而不是 P.Id。 P.Id 没有出现在 SELECT 语句中，虽然 MySQL 允许这样做，但结果由编译器决定，这可能会给出你出乎意料。