mysql - 如何返回一个多选 SQL 查询，并在平面行中包含一些连接

Question

我正在尝试按邮政编码区域返回用户类型列表。例如，我有一个“membertype”列，可能是 X、Y 或 Z。在邮政编码区域 G83 中，我可能有多个类型 X 和多个类型 Y。我希望返回如下结果

|邮政编码 | X |是 |

| G83 | 9 | 3 |

现在尝试一下

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

我想要一个可以循环的响应，以在我的应用程序中输出表格。

Answer 1

您可以使用“FIELDS TERMINATED BY”:

SELECT substring(postcode, 1, locate (' ', postcode) - 1) AS postcode, count(u.membertype) AS cook, count(u2.membertype) AS customer FROM user_locations AS ul
LEFT JOIN users AS u ON u.id = ul.user_id
LEFT JOIN users AS u2 ON u2.id = ul.user_id
WHERE u.membertype = "seller"
OR u2.membertype = "customer"
GROUP BY postcode
INTO OUTFILE '/var/lib/mysql-files/files.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'