sql group_concat 和子查询

sql group_concat and subquery(sql group_concat 和子查询)
本文介绍了sql group_concat 和子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有 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

我想像这样获取这些数据:

And I want to fetch this data like this:

12    12,45,67,78
13    13.12.67,105

我像这样使用 group_concat 和 group by:

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

问题:如果 ids - 例如对于 12、45、67、78,如何从 car_model 表中获取此字符串的标题.我想在 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 和子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)
SQL/MySQL: split a quantity value into multiple rows by date(SQL/MySQL:按日期将数量值拆分为多行)