php - Mysql 我正在合并表。但我不能做过滤排序

Question

我用 group_concat 组合表格。但我不能做过滤排序。我该怎么做？按 ads_filter_option.filter_option asc 或 desc 排序无效。

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

Answer 1

这似乎有效。这个结果与您尝试做的有什么不同？

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 ',')

如果您还想按选项类型排序，试试这个:

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 ads_filter.filter_title,GROUP_CONCAT(DISTINCT ads_filter_option.filter_option SEPARATOR ',')

这是第二次查询的结果:

| id |                                                    ad_title | GROUP_CONCAT(DISTINCT ads_filter.filter_title SEPARATOR ',') | GROUP_CONCAT(DISTINCT ads_filter_option.filter_option SEPARATOR ',') |
|----|-------------------------------------------------------------|--------------------------------------------------------------|----------------------------------------------------------------------|
| 64 |                   Kiralık Araçlar deneme reyonsiverek ilanı |                                                        Color |                                                                Black |
| 59 |                          Otomobil deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 61 |              Arazi, SUV & Pick-up deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 63 |                    Ticari Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 65 |                   Hasarlı Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 66 |                    Deniz Araçları deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 69 |                  Modifiye Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 71 |                               ATV deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 72 |                               UTV deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 74 |           Engelli Plakalı Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,1.6 Engine |
| 62 |           Minivan, Van & Panelvan deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,2.0 Engine |
| 67 |                    Klasik Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,2.0 Engine |
| 73 |                           Karavan deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     Black,2.0 Engine |
| 70 |                     Hava Araçları deneme reyonsiverek ilanı |                                                        Color |                                                                White |
| 47 | 1 AMG KIRMIZI HATASIZ SERVİS BAKIMLI CLA 200 İLK SAHİBİNDEN |                                                 Color,Engine |                                                     White,2.0 Engine |
| 60 |                        Motosiklet deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     White,2.0 Engine |
| 68 |                Elektrikli Araçlar deneme reyonsiverek ilanı |                                                 Color,Engine |                                                     White,2.0 Engine |