对给定方案的国家和城市数据进行排序查询

Question

我的输入数据如下：

**Country          city**
Australia     Sydney
Australia     melbourne
India         Delhi
India         Chennai
India         Bangalore
Afghanistan   Kabul

预期输出为：

Afghanistan
Kabul
Australia
melbourne
syndey
India
Bangalore
Chennai
Delhi

两列中的数据应按字母顺序排列(城市级别和国家级别(，结果应为具有上述值的单列。国家应按字母顺序排列，相应的城市应位于其下方，也应按字母顺序排列。 如何在单个查询中不使用中间表的情况下完成此操作？

Answer 1

您需要一个UNION ALL查询才能在结果中获取每个国家/地区一行和每个城市一行：

select coalesce(city, country) as location
from
(
select distinct country, null as city from mytable
union all
select country, city from mytable
)
order by country, city nulls first;

Answer 2

这具有单个表扫描，也不需要使用UNION来获得不同的结果：

SQL 小提琴

Oracle 11g R2 架构设置：

CREATE TABLE cities ( Country, city ) AS
SELECT 'Australia',     'Sydney' FROM DUAL UNION ALL
SELECT 'Australia',     'melbourne' FROM DUAL UNION ALL
SELECT 'India',         'Delhi' FROM DUAL UNION ALL
SELECT 'India',         'Chennai' FROM DUAL UNION ALL
SELECT 'India',         'Bangalore' FROM DUAL UNION ALL
SELECT 'Afghanistan',   'Kabul' FROM DUAL;

查询 1：

SELECT value
FROM   (
SELECT c.*,
country AS ctry,
ROW_NUMBER() OVER ( PARTITION BY Country ORDER BY city ) AS rn
FROM   cities c
)
UNPIVOT( value FOR key IN ( Country AS 1, City AS 2 ) )
WHERE rn = 1 OR key = 2
ORDER BY ctry, rn, key

结果：

|       VALUE |
|-------------|
| Afghanistan |
|       Kabul |
|   Australia |
|      Sydney |
|   melbourne |
|       India |
|   Bangalore |
|     Chennai |
|       Delhi |