带有逗号分隔结果的 MySQL View

Question

我正在 MySQL Workbench 中创建一个 View :

表格:

我想创建一个 View ，它将以这样的方式返回记录，我从 addressBook 表和 data field 中获取所有行，值用逗号分隔 addressBookData 表。

怎么做？

我的看法是这样的:

CREATE VIEW `addressBook_view` AS
SELECT
    `addressBook`.*,
    (SELECT `addressBookData`.`data` FROM `addressBookData` WHERE `addressBook_id` = `id` AND `type` = 'mobile') AS `mobile`,
    (SELECT `addressBookData`.`data` FROM `addressBookData` WHERE `addressBook_id` = `id` AND `type` = 'phone') AS `phone`,
    (SELECT `addressBookData`.`data` FROM `addressBookData` WHERE `addressBook_id` = `id` AND `type` = 'fax') AS `fax`
FROM
    `addressBook`

Answer 1

尝试这样的事情:

SELECT ab.*, abd.type, GROUP_CONCAT(abd.data SEPARATOR ',')
FROM addressBook ab 
LEFT JOIN addressBookData abd ON ab.id = abd.addressBook_id
GROUP BY ab.id, abd.type

您可以自己根据这个查询创建 View 。