mysql - 修复“子查询返回多于1行”的错误

This question already has answers here:

multiple query same table but in different columns mysql
                                
                                    （4个答案）
                                
                        
                                12个月前关闭。
            
                    
我有一个查询，当只有一种类型的“按组”响应时，该查询恰好返回我想要的内容。当有多个时，出现“子查询返回多于1行”错误。

我尝试了多种结构化查询的方法，包括使用“ in”，但无法进行任何工作。

SELECT
    (
    SELECT substring(postcode, 1, locate (' ', postcode) - 1)
    FROM user_locations AS ul
    JOIN users AS u ON u.id = ul.user_id
    WHERE u.membertype = "customer"
    ) AS postcode,
    (
    SELECT count(u.membertype)
    FROM users AS u
    JOIN user_locations AS ul ON u.id = ul.user_id
    WHERE u.membertype = "cook"
    GROUP BY ul.postcode

    ) AS cook,
    (
    SELECT count(u.membertype)
    FROM users AS u
    JOIN user_locations AS ul ON u.id = ul.user_id
    WHERE u.membertype = "customer"
    GROUP BY ul.postcode
    ) AS customer

我希望看到多个邮政编码

Postcode | Cooks | Customers
G83      | 12    | 34
G84      | 19    | 76
G85      | 10    | 50

最佳答案

您可以使用表达式u.membertype=xxx在单个查询中执行此操作，如果为true，则为1，否则为0。

SELECT ul.postcode AS "Postcode",
       SUM(u.membertype="cook") as "Cooks"
       SUM(u.membertype="customer") as "Customers"
FROM users AS u
JOIN user_locations AS ul ON u.id = ul.user_id
GROUP BY ul.postcode

如果存在两种以上成员类型，则可以添加WHERE u.membertype IN ("cook","customer")。

关于mysql - 修复“子查询返回多于1行”的错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54525544/