Группировка вывода дочерних разделов в родительские Mysql
Имеются города city, автомойки wash и посты на них bfrs.
http://sqlfiddle.com/#!9/c97795
Как вывести список с группировкой постов по городам и автомойкам, типа:
Paris:
«Monmartre»
1. BFR-5
«Mouline Rouge»
1. BFR-11
2. BFR-201
Bogota....
Ответы (1 шт):
Автор решения: Akina
→ Ссылка
SELECT CASE WHEN wash_addr IS NULL THEN city_name
WHEN bfrs_name IS NULL THEN CONCAT('-',wash_addr)
ELSE CONCAT('--',bfrs_name)
END name
FROM ( SELECT city.name city_name, null wash_addr, null bfrs_name
FROM city
UNION ALL
SELECT city.name, wash.addr, null
FROM city
JOIN wash ON city.pref = wash.city
UNION ALL
SELECT city.name, wash.addr, bfrs.name
FROM city
JOIN wash ON city.pref = wash.city
JOIN bfrs ON city.pref = bfrs.city AND wash.id = bfrs.wash
) subquery
ORDER BY city_name, wash_addr IS NOT NULL, wash_addr, bfrs_name IS NOT NULL, bfrs_name;
если в каком-то городе нет мойки (или постов) его не выводить. – kilotonna
SELECT CASE WHEN wash_addr IS NULL THEN city_name
WHEN bfrs_name IS NULL THEN CONCAT('-',wash_addr)
ELSE CONCAT('--',bfrs_name)
END name
FROM ( SELECT DISTINCT city.name city_name, null wash_addr, null bfrs_name
FROM city
JOIN wash ON city.pref = wash.city
JOIN bfrs ON city.pref = bfrs.city AND wash.id = bfrs.wash
UNION ALL
SELECT DISTINCT city.name, wash.addr, null
FROM city
JOIN wash ON city.pref = wash.city
JOIN bfrs ON city.pref = bfrs.city AND wash.id = bfrs.wash
UNION ALL
SELECT city.name, wash.addr, bfrs.name
FROM city
JOIN wash ON city.pref = wash.city
JOIN bfrs ON city.pref = bfrs.city AND wash.id = bfrs.wash
) subquery
ORDER BY city_name, wash_addr IS NOT NULL, wash_addr, bfrs_name IS NOT NULL, bfrs_name;