mysql group_concat 字段中的排序过滤器 [英] Sorting filters in mysql group_concat field

问题描述

我可以按颜色对 mysql group_concat 字段进行排序.但我不能按发动机功率排序.我能为它做什么?颜色或引擎顺序?

I can sort the mysql group_concat field by color. But I can not sort by motor engine power. What can I do for it? color or engine order by ?

SQL-FIDDLE-TABLE

select
ads.id,
ads.ad_title,
GROUP_CONCAT(DISTINCT ads_filter.filter_title SEPARATOR ','),
GROUP_CONCAT(DISTINCT ads_filter_option.filter_option SEPARATOR ',')
from ads
LEFT JOIN ads_filter_option
on find_in_set(ads_filter_option.id,ads.filter_option_id)
LEFT JOIN ads_filter
on ads_filter.id=ads_filter_option.filter_id
where cat_1 = 2
group by ads.id
order by GROUP_CONCAT(DISTINCT ads_filter_option.filter_option SEPARATOR ',')

预期结果:

White,2.0 Engine
White,2.0 Engine
Black,2.0 Engine
Black,2.0 Engine
Black,2.0 Engine
Black,2.0 Engine
Black,2.0 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
Black,1.6 Engine
White,1.6 Engine
White,1.6 Engine

推荐答案

您可以先使用子查询 group_concat 按引擎功率，然后使用列别名进行排序.

You can use a sub-query to group_concat by engine power first then use column alias to order by.

For example:
select
id,
ad_title,
filter_title,
filter_option_color
from (
   select
   ads.id,
   ads.ad_title,
   GROUP_CONCAT(DISTINCT ads_filter.filter_title order by ads_filter.filter_title SEPARATOR ',') filter_title,
   GROUP_CONCAT(DISTINCT ads_filter_option.filter_option order by ads_filter_option.filter_option SEPARATOR ',') filter_option_power,
   GROUP_CONCAT(DISTINCT ads_filter_option.filter_option order by ads_filter_option.filter_option desc SEPARATOR ',') filter_option_color
   from ads
   LEFT JOIN ads_filter_option
   on find_in_set(ads_filter_option.id,ads.filter_option_id)
   LEFT JOIN ads_filter
   on ads_filter.id=ads_filter_option.filter_id
   where cat_1 = 2
       group by ads.id ) t
order by filter_title, filter_option_power desc;

Result:
id  ad_title                                        filter_title    filter_option
60  Motosiklet deneme reyonsiverek ilanı            Color,Engine    White,2.0 Engine
68  Elektrikli Araçlar deneme reyonsiverek ilanı    Color,Engine    White,2.0 Engine
59  Otomobil deneme reyonsiverek ilanı              Color,Engine    Black,2.0 Engine

这篇关于mysql group_concat 字段中的排序过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！