Вывести данные с чередованием в Postgresql

Question

Всем привет! У меня есть такой код:

CREATE TABLE COUNTRY (
  Id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE CITY (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  population INTEGER NOT NULL,
  contryID INTEGER NOT NULL
);
INSERT INTO COUNTRY VALUES (0001, 'Russia');
INSERT INTO COUNTRY VALUES (0002, 'USA');
INSERT INTO COUNTRY VALUES (0003, 'Italy');
INSERT INTO CITY VALUES (0001, 'Moskov', 10, 0001);
INSERT INTO CITY VALUES (0002, 'Piter', 5,  0001);
INSERT INTO CITY VALUES (0003, 'Rostov', 1, 0001);
INSERT INTO CITY VALUES (0004, 'New York', 8, 0002);
INSERT INTO CITY VALUES (0005, 'Los Angeles', 4, 0002);
INSERT INTO CITY VALUES (0006, 'Las Vegas', 3, 0002);
INSERT INTO CITY VALUES (0007, 'Rome', 7, 0003);
INSERT INTO CITY VALUES (0008, 'Florentia', 5, 0003);
INSERT INTO CITY VALUES (0009, 'Vinecia', 3, 0003);

SELECT name, NULL as population FROM COUNTRY
 UNION ALL
SELECT name, population FROM CITY

Если запустить этот код то получаю следующее:

name	population
Russia
USA
Italy
Moskov	10
Piter	5
Rostov	1
New York	8
Los Angeles	4
Las Vegas	3
Rome	7
Florentia	5
Vinecia	3

А как сделать, чтоб было как указано в таблице ниже(при этом суммируя население для страны в виде итоговой строки после группы городов каждой страны и в самом конце итог по всем странам)?:

name	population
Russia
Moskov	10
Piter	5
Rostov	1
ИТОГ:Russia	16
USA
New York	8
Los Angeles	4
Las Vegas	3
ИТОГ:USA	15
Italy
Rome	7
Florentia	5
Vinecia	3
ИТОГ:Italy	15
ИТОГ:World	46

Подойдет любой способ(Производительность имеет значение)

Answer 1

Кажется вам нужно что-то вроде этого:

select 
  country.name, 
  city.name, 
  sum(population) 
from 
  country, 
  city 
where 
  country.id = city.countryid 
group by 
  grouping sets (
    (country.name), 
    (city.name), 
    ()
  ) 
order by 
  city.name nulls last, 
  country.name nulls last;

Результат же экспортируете в эксель (или обрабатываете в любом знакомом вам языке программирования) и дооформляете до нужного вам вида: писать слово "ИТОГ" и смешивать колонки названия страны и города - это не задача для PostgreSQL в общем случае.

Answer 2

Я сделал так

WITH selector as ( 
SELECT name, id, id as main,null as inside, NULL AS population
FROM COUNTRY
UNION ALL
SELECT name, contryID, null, contryID, population
FROM CITY
UNION ALL
SELECT 'ИТОГ:' || country.name, country.id,null, NULL, 
SUM(city.population)
FROM COUNTRY country
JOIN CITY city ON country.id = city.contryID
GROUP BY country.name, country.id
UNION ALL
SELECT 'ИТОГ:World', null,null, NULL, SUM(population)
FROM CITY
ORDER BY id, main, inside)
SELECT name, population  from selector

Если кто знает как это можно улучшить, сообщите плз

Answer 3

select case when ci_name is null and co_name is not null then 'Итого: '||co_name when ci_name is null and co_name is null then 'Итого: World' else co_name  end
,ci_name
,s_pop 
from (select COUNTRY.name as co_name,CITY.name as ci_name,sum(CITY.population) as s_pop 
  from COUNTRY join CITY on (COUNTRY.id=CITY.contryid) group by 
  rollup(COUNTRY.name,CITY.name) 
  order by COUNTRY.name,CITY.name) as f;

co_name	ci_name	s_pop
Italy	Florentia	5
Italy	Rome	7
Italy	Vinecia	3
Итого: Italy		15
Russia	Moskov	10
Russia	Piter	5
Russia	Rostov	1
Итого: Russia		16
USA	Las Vegas	3
USA	Los Angeles	4
USA	New York	8
Итого: USA		15
Итого: World		46

(13 rows)

БЛОГ НА HUSL

Вывести данные с чередованием в Postgresql

Ответы (3 шт):