如何在MySQL中将子查询的字符串连接成单行？

Question

我有三个表：

table "package"
-----------------------------------------------------
package_id      int(10)        primary key, auto-increment
package_name    varchar(255)
price           decimal(10,2)

table "zones"
------------------------------------------------------
zone_id         varchar(32)    primary key (ex of data: A1, Z2, E3, etc)

table "package_zones"
------------------------------------------------------
package_id     int(10)
zone_id        varchar(32)

我想要的是返回包表(package table)中所有信息以及该包的区域列表。 我希望区域列表按字母顺序排序并用逗号分隔。

所以我期望得到的输出应该类似于这样：

+------------+---------------+--------+----------------+
| package_id | package_name  | price  | zone_list      |
+------------+---------------+--------+----------------+
| 1          | Red Package   | 50.00  | Z1,Z2,Z3       |
| 2          | Blue Package  | 75.00  | A2,D4,Z1,Z2    |
| 3          | Green Package | 100.00 | B4,D1,D2,X1,Z1 |
+------------+---------------+--------+----------------+

我知道可以在PHP的表现层做一些事情来获得所需的结果。问题是，我想能够按zone_list ASC或DESC排序，甚至使用WHERE zone_list LIKE等等。为了做到这一点，我需要在MySQL中完成。

我甚至不知道该如何开始解决这个问题。我尝试使用子查询，但它一直抱怨有多行。我尝试将多行concat成一个字符串，但显然MySQL不喜欢这样。

提前谢谢。

---

更新！

以下是解决方案，供有兴趣的人参考：

SELECT 
    `package`.*,
    GROUP_CONCAT(`zones`.`zone` ORDER BY `zones`.`zone` ASC SEPARATOR ','  )  as `zone_list`
FROM 
    `package`,
    `package_zones`
LEFT JOIN 
    (`zones`,`ao_package_zones`) ON (`zones`.`zone_id` = `package_zones`.`zone_id` AND `package_zones`.`package_id` = `package`.`package_id`)
GROUP BY 
    `ao_package`.`package_id`

- mrbinky3000

---

使用group_concat和zone字段进行分组。 - Haim Evgi

我使用了你的问题更新部分：select roles.*，GROUP_CONCAT(rp.permission_id SEPARATOR ',') as permission_ids from roles join role_permissions rp on roles.id = rp.role_id GROUP BY roles.id。它可以工作。 - Ozal Zarbaliyev

Answer 1

通过使用GROUP_CONCAT()函数和 GROUP BY 操作，可以实现将多行数据合并成一行。以下是一个示例查询：

SELECT 
   p.package_id,
   p.package_name,
   p.price,
   GROUP_CONCAT(pz.zone_id SEPARATOR ',') as zone_list 
FROM 
   package p 
LEFT JOIN package_zone pz ON p.package_id = pz.package_id 
GROUP BY 
   p.package_id

你仍然可以按照 zone_id s（或 zone_list）排序，而不是使用 LIKE，你可以使用 WHERE zp.zone_id = 'Z1' 或类似的东西。

- GSto

---

快了，差不多就到了！我想要 zone_list 按字母顺序排序。这就是为什么我更倾向于使用子查询。不过已经非常接近了。 - mrbinky3000

发现我可以在GROUP CONCAT中使用"ORDER BY"。所以，GROUP_CONCAT(pz.zone_id ORDER BY pz.zone_id ASC SEPARATOR ',')就可以工作了！！！ - mrbinky3000

修订版：无法工作。它总是返回每行的所有可能区域。每行应仅返回该特定软件包的区域。 - mrbinky3000

我找到了如何防止返回所有可能的行的方法。你必须进行交叉连接。我会把答案放在问题中让大家看到。 - mrbinky3000

任何想要在SQL Server中执行此操作的人可以参考以下链接：https://dev59.com/T3VD5IYBdhLWcg3wWaRh - Mahmood Dehghan

完美...需要添加group by来指定我想分组的列，否则它会默认为你正在group_concat的那一列！ - Andrew

Answer 2

我有一个客户要求我生成一个查询，用于在prestashop数据库中获取ps_orders、ps_customer表中的去年记录；其中一个列是来自ps_order_detail表的“订单中产品ID列表”。因此，我的查询结果如下：

SELECT 
    o.id_order, o.reference, 
    (
        SELECT 
            GROUP_CONCAT(od.product_id SEPARATOR ', ') AS products 
        FROM ps_order_detail od 
        WHERE od.id_order = o.id_order 
        GROUP BY od.id_order
    ) AS product_ids,
    CONCAT(c.firstname, ' ', c.lastname) AS customer, o.current_state, o.payment, o.total_discounts, 
    o.total_discounts_tax_incl, o.total_discounts_tax_excl, o.total_paid, o.total_paid_tax_incl,
    o.total_paid_tax_excl, o.total_products, o.total_products_wt, o.total_shipping, o.total_shipping_tax_incl, 
    o.total_shipping_tax_excl, o.carrier_tax_rate, o.total_wrapping, o.total_wrapping_tax_incl, 
    o.total_wrapping_tax_excl, o.invoice_number, o.delivery_number, o.valid
FROM ps_orders o
INNER JOIN ps_customer c ON c.id_customer = o.id_customer
WHERE 
    o.date_add <= NOW() AND o.date_add >= DATE_ADD(NOW(), INTERVAL - 12 MONTH);

结果：

- Carlos Espinoza