mysql连多个表group_mysql – 表连接多个group_concat

关于使用group_concat连接表有问题.这是详细信息.

table_orders：

item_cd order_id descs quantity status seq_no

1 100 coca-cola 2 A 232

2 100 pizza 1 A 233

3 101 cheeseburger 5 A 234

4 102 pepsi 4 A 235

4

table_instructions：

item_cd instruction

3 more cheese

3 less vegetable

cancelled_item_table：

quantity seq_no

1 234

1 234

1 235

现在我想要实现的是这样的：

item_cd descs quantity instructions cancelled_item

1 coca-cola 2 - -

2 pizza 1 - -

3 cheeseburger 2 more cheese, less vegetable 1,1

4 pepsi 4 - 1

这是我目前的查询：

SELECT

ord.item_cd,

ord.order_id,

ord.descs,

ord.quantity,

GROUP_CONCAT(x.quantity) as cancelled,

GROUP_CONCAT(i.instruction) as instruct

FROM table_orders ord

LEFT JOIN cancelled_item_table x ON ord.seq_no = x.seq_no

LEFT JOIN table_instructions i ON ord.item_cd = i.item_cd

WHERE ord.status = 'A'

GROUP BY ord.order_id

这是输出：

item_cd descs quantity instructions cancelled_item

1 coca-cola 2 - 1

2 pizza 1 - 1

3 cheeseburger 2 more cheese, more cheese,

less vegetable, less vegetable 1,1,1,1

4 pepsi 4 - 1

如果你注意到,芝士汉堡有2个取消的项目和2个指令,但输出是4,看起来它正在成倍增加.