mysql组\u concat with where in condition

x8goxv8g 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(368)

我有三桌产品

ID name
1  A
2  B
3  C

小贩

ID Name
1  V1
2  V2
3  V3

产品供应商

我想要左连接并创建如下表所示的响应

p_id  product  vendors
1     A        V1,V2
2     B        V3
3     C        V1,V2,V3

下面是我的解决方案，但它相当复杂。有没有可能对此进行简短的查询

SELECT GROUP_CONCAT(v2.`name`) AS vendername,
       p.*,
       GROUP_CONCAT(v.`v_id`) AS vi
FROM `products` p
JOIN `product_vendor` v ON p.id = v.p_id,
                           `vendors` v2
WHERE v2.id IN
    (SELECT GROUP_CONCAT(p.`id`) AS vi
     FROM `vendors` p
     JOIN `product_vendor` v ON p.id = v.p_id
     GROUP BY p.id)
GROUP BY p.id
ORDER BY `p_id` ASC

mysql

来源：https://stackoverflow.com/questions/50814932/mysql-group-concat-with-where-in-condition

2条答案

按热度按时间

vdzxcuhz1#

你能试试这个吗，我还没有执行查询，但我认为它会有帮助：

SELECT `P`.`id`, `P`.`name`, CONCAT(GROUP_CONCAT(CAST(`V`.`name` AS CHAR)), ", ") as vendername 
FROM `product` P 
JOIN `product_vendor` PV ON (`PV`.`pid` = `P`.`id`) 
JOIN `vendor` V ON (`V`.`ID` = `PV`.`vid`) 
GROUP BY `PV`.`pid`

要列出产品表中的所有记录，请使用：

SELECT `P`.`id`, `P`.`name`, CONCAT(GROUP_CONCAT(CAST(`V`.`name` AS CHAR)), ", ") as vendername 
FROM `product` P 
LEFT JOIN `product_vendor` PV ON (`PV`.`pid` = `P`.`id`) 
LEFT JOIN `vendor` V ON (`V`.`ID` = `PV`.`vid`) 
GROUP BY `PV`.`pid`
ORDER BY `P`.`id`

赞(0）回复(0）举报 2021-06-21

qni6mghb2#

只要用连接和 group_concat 不需要子查询

SELECT 
    p.id, 
    p.name, 
    Group_concat(v.name) AS vendername 
FROM
    product p 
JOIN 
    product_vendor pv ON p.id = pv.pid 
JOIN 
    vendor v ON pv.vid = v.id 
GROUP BY 
    p.id, p.name 
ORDER BY 
    p.id ASC

演示

赞(0）回复(0）举报 2021-06-21

我来回答

mysql组\u concat with where in condition

2条答案

相关问题

热门标签

最新问答