mysql - 在 CONCAT 字符串中排序结果

Question

我有一张新车和二手车表:

id  country     status  car
1   Australia   new     Honda
2   Australia   used    Alfa Romeo
3   Australia   used    Jaguar
4   Australia   used    BMW
5   Belgium     new     BMW
6   Belgium     new     Citroen
7   Belgium     used    Honda

我想统计每个国家每辆二手车和新车的数量，然后像这样显示结果:

country     new                    used 
Australia   1 new: Honda           3 used including Alfa Romeo  
Belgium     2 new including BMW    1 used: Honda    

换句话说，如果每个国家/地区有多个"new"或“二手”，则将它们连接成一个字符串，其中包含第一个 汽车名称(按 id，优先)。

这是我的代码:

SELECT t1.country, 
CASE 
    WHEN LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "new", "")) = 1 THEN CONCAT("1 new: ", t1.car)
    WHEN LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "new", "")) > 1 THEN CONCAT(LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "new", "")), " new including ", t1.car)
    ELSE ''
END as new, 
CASE 
    WHEN LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "used", "")) = 1 THEN CONCAT("1 used: ", t1.car)
    WHEN LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "used", "")) > 1 THEN CONCAT(LENGTH(group_concat(t1.status)) - LENGTH(REPLACE(group_concat(t1.status), "used", "")), " used including ", t1.car)
    ELSE ''
END as noms
from table1 t1
group by t1.country

但是，这些是我的结果:

country     new                        used 
Australia   1 new: Honda               3 used including Honda
Belgium     2 new including BMW        1 used: BMW  

在这些结果中，如果每个国家/地区有多个"new"或“二手”，它只是向我显示第一辆汽车，而不管状态如何。

如果有多个结果，我如何让它显示每个状态的第一辆车？

Answer 1

您可以查看此查询 here .

SELECT country,
  CASE
    WHEN COUNT(IF(status = 'new', id, NULL)) = 1 THEN CONCAT('1 new: ', GROUP_CONCAT(IF(status = 'new', car, NULL)))
    WHEN COUNT(IF(status = 'new', id, NULL)) > 0 THEN CONCAT(COUNT(IF(status = 'new', id, NULL)), ' new including ', SUBSTRING_INDEX(GROUP_CONCAT(IF(status = 'new', car, NULL) ORDER BY id SEPARATOR ';'), ';', 1))
    ELSE 'no new cars'
  END AS new_cars,
  CASE
    WHEN COUNT(IF(status = 'used', id, NULL)) = 1 THEN CONCAT('1 used: ', GROUP_CONCAT(IF(status = 'used', car, NULL)))
    WHEN COUNT(IF(status = 'used', id, NULL)) > 0 THEN CONCAT(COUNT(IF(status = 'used', id, NULL)), ' used including ', SUBSTRING_INDEX(GROUP_CONCAT(IF(status = 'used', car, NULL) ORDER BY id SEPARATOR ';'), ';', 1))
    ELSE 'no used cars'
  END AS used_cars
FROM cars
GROUP BY country

您可以在 GROUP_CONCAT 中使用 ORDER BY 和 SEPARATOR .需要第一辆车的ID？使用 ORDER BY id 和 SUBSTRING_INDEX使用适当的分离器提取第一辆车。