Mysql - 分组和连接多列

Question

我有这样的数据:

| hotel_id | city_name_en | country_name_en      | city_name_ar | country_name_ar |
|----------|--------------|----------------------|--------------|-----------------|
| 4020874  | Dubai        | United Arab Emirates | XXXX         | XXXXXXXX        |
| 4020882  | Dubai        | United Arab Emirates | XXXX         | XXXXXXXX        |
| 4020890  | Dubai        | United Arab Emirates | XXXX         | XXXXXXXX        |
| 4000053  | Al Fujayrah  | United Arab Emirates | YYYY         | YYYYYYYY        |
| 4000058  | Al Fujayrah  | United Arab Emirates | YYYY         | YYYYYYYY        |
| 4000060  | Al Fujayrah  | United Arab Emirates | YYYY         | YYYYYYYY        |

我想要这样的输出:

| hotel_id_list             | name_en                           | name_ar            |
|---------------------------|-----------------------------------|--------------------|
| 4020874, 4020882, 4020890 | Dubai, United Arab Emirates       | XXXX, XXXXXXXXXXXX |
| 4000053, 4000058, 4000060 | Al Fujayrah, United Arab Emirates | YYYY, YYYYYYYYYYYY |

我想连接酒店 ID 并按 city_name_en 和 city_name_ar 对它们进行分组。

这是我到目前为止尝试过的查询。

select  
        GROUP_CONCAT(DISTINCT hotel_id) AS 'hotel_id_list'
from (
select g1.hotel_id, g1.country_code, g1.city_name as city_name_en, g1.country_name as country_name_en, g2.city_name as city_name_ar, g2.country_name as country_name_ar
from general_details g1
inner join general_details g2 on g1.hotel_id = g2.hotel_id and g1.language_id=0 and g2.language_id=1) as t
where country_code = 'AE'

我可以连接酒店 ID，但我也想连接城市和国家/地区。

Answer 1

像这样的东西应该可以工作:

SELECT GROUP_CONCAT(`hotel_id`) AS `hotel_id_list`
CONCAT(`city_name_en`, ', ', `country_name_en`) AS `name_en`
CONCAT(`city_name_ar`, ', ', `country_name_ar`) AS `name_ar`
FROM `<yourtablename>`
GROUP BY `city_name_en`, `country_name_en`, `city_name_ar`, `country_name_ar`