sql group_concat和子查询 [英] sql group_concat and subquery
问题描述
我有2个mysql表:
I have 2 mysql tables:
car_model:
id (int) (Primary Key)
title (varchar)
id_brand (int) FK to car_brand table
car__car_model: - relation many to many
id_model (int)
id_advice_model (int)
在car__car_model中,有以下数据:
In car__car_model there are the following data:
(id_model) (id_advice_model)
12 12
12 45
12 67
12 78
13 13
13 12
13 67
13 105
我想这样获取此数据:
12 12,45,67,78
13 13.12.67,105
我使用group_concat并按如下方式分组:
I use group_concat and group by like this:
SELECT ccm.id_model,group_concat(ccm.id_advice_model) FROM car__car_model as ccm group by ccm.id_model
问题: 如果是id,如何从car_model表中获取该字符串的标题-例如12,45,67,78.我想在1个查询中执行此操作-自定义我的查询
Question: How can fetch titles from car_model table for this string if ids - for example for 12,45,67,78. And I want to do it in 1 query - to customize my query
已更新: 现在我有另一个问题: 我还有一张桌子:
Updated: And Now I have another question: I Have one more table:
car_brand:
id (int) (PK)
title (varchar)
在我的car_model表中,有一个字段 id_brand
and in my car_model table there is a field id_brand
问题2: 我如何从car_model中获取带有car_brand标题的标题表格-像这样-福特福克斯,菲亚特Linea等-现在,我可以(在您的帮助下)仅从car_model中获取标题 现在我有:12-Focus,Linea
Question 2: How can i fetch title form car_brand with the title from car_model - like this - Ford Focus, Fiat Linea and so on - Now I get (with you help) to fetch only title from car_model Now I have: 12 - Focus,Linea
已更新: 我使用-
SELECT
id_model,group_concat(concat(cb.title,' ',cm.title))
FROM
car__car_model as ccm
inner join car_model cm on (cm.id = ccm.id_advice_model)
inner join car_brand cb on (cb.id=cm.id_brand)
group by ccm.id_model
推荐答案
尝试一下::
SELECT
ccm.id_model,group_concat(cm.tile, SEPARATOR ',')
FROM
car__car_model as ccm
inner join car_model cm on (cm.id = ccm.id_advice_model)
group by ccm.id_model
这篇关于sql group_concat和子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!