来自其他两个表的Update中的group_concat的MySQL语句

由 et_et发布于 2020-05-09 08:14:21

I have 3 tables below I'm trying to write a group_concat statement for the "item" column in the table cart_session

TABLE products

id      name
------------------------
123  |  car
456  |  boat
789  |  house

--------------------------------

TABLE cart_items

pid session
----------------------
123  |  ABCD    
456  |  ABCD
789  |  EFGH

----------------------------------

TABLE cart_session

id   item
----------------------------
ABCD  |  car,boat
EFGH  |  house

雅典娜

2020-05-09 08:14:22

您可以使用update / join语法，如下所示：

update cart_session cs
inner join (
    select ci.session, group_concat(p.name) item
    from products p
    inner join cart_items ci on ci.pid = p.pid
    group by ci.session
) x on x.session = cs.session
set cs.item = x.item

另一个选项使用相关的子查询：

update cart_session cs
set cs.item = (
    select group_concat(p.name)
    from products p
    inner join cart_items ci on ci.pid = p.pid
    where ci.session = cs.session
)

This is slightly different: this query would assign a null value to rows in cart_session that have no match in the other tables, while the first query leaves such rows intact.

et_et

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3172
注册排名
1539