mysql - MySQL View 以逗号分隔结果

我正在MySQL工作台中创建一个视图：
桌子：

我想创建一个返回记录的视图，在这个视图中，我从addressBook表中获取所有行，并从addressBook data表中以逗号分隔的数据字段值。
怎么做？
我的观点是这样的：

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`

最佳答案

试试这样的：

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

您可以自己基于此查询创建视图。

关于mysql - MySQL View 以逗号分隔结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37546220/