在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

實戰(zhàn) SQL：實現(xiàn)百度、高德等地圖中的地鐵換乘線路查詢

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        簡單 CTE
        生成數(shù)字序列
        遍歷組織結(jié)構圖
        查找地鐵換乘線路
1
2
3
4

對于大多數(shù)人來說，SQL 意味著 SELECT、INSERT、UPDATE 和 DELETE。但實際上，SQL 能夠?qū)崿F(xiàn)的功能遠遠不止簡單的增刪改查；今天我們來介紹一個高級功能：通用表表達式（Common Table Expression）。CTE 可以提高復雜查詢的性能和可讀性，實現(xiàn)樹狀結(jié)構或者圖數(shù)據(jù)的遍歷。例如：

生成數(shù)字序列；
獲取員工上下級的組織關系；
查詢地鐵、航班換乘線路；
社交網(wǎng)絡圖譜分析。
1
2
3
4

一般來說，我們只能通過應用程序或者存儲過程實現(xiàn)這些復雜的功能，而且性能不高。但是有了 CTE，我們可以直接利用一個 SQL 語句完成以上功能。CTE 不僅強大而且通用，各種主流數(shù)據(jù)庫都提供了支持。

我們通過幾個實用案例，了解一下 CTE 的語法，同時介紹各種數(shù)據(jù)庫中的實現(xiàn)差異。
簡單 CTE

通用表表達式使用WITH關鍵字表示：

WITH t(n) AS (
SELECT 4 – – Oracle
)
SELECT * FROM t;

以上 WITH 子句相當于定義了一個語句級別的臨時表 t(n)，在隨后的 SELECT、INSERT、UPDATE 以及 DELETE 語句中都可以使用。如果是 Oracle，可以使用SELECT 2 FROM dual。

??WITH 子句定義了一個表達式，表達式的值是一個表，所以稱為通用表表達式。CTE 和子查詢類似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 語句。Oracle 中稱之為子查詢因子（subquery factoring）。
1

CTE 與子查詢類似，只在當前語句中有效；不過一個語句中可以定義多個 CTE，而且 CTE 被定義之后可以多次引用：

WITH t1(n) AS (
SELECT 4 – FROM dual
),
t2(n) AS (
SELECT n+1 FROM t1
)
SELECT t1.n, t2.n
FROM t1
CROSS JOIN t2;

第一個 CTE 名為 t1；第二個 CTE 名為 t2，引用了前面定義的 t1 ；每個 CTE 之間使用逗號進行分隔；最后的 SELECT 語句使用前面定義的 2 個 CTE 進行連接查詢。這種使用 CTE 的方法和編程語言中的變量非常類似。

CTE 和視圖、臨時表或者子查詢都有點類似，但是比它們的結(jié)構更加清晰；數(shù)據(jù)庫對于 CTE 只需要執(zhí)行一次，性能也會更好。不過，CTE 真正強大之處是允許在定義中調(diào)用自己，也就是遞歸調(diào)用。
生成數(shù)字序列

numbers
WITH子句還有一種遞歸形式，以下語句可以生成一個 1 到 10 的數(shù)字序列：

WITH RECURSIVE t(n) AS
(
SELECT 1 – 初始化
UNION ALL
SELECT n + 1 FROM t WHERE n < 10 – 遞歸結(jié)束條件
)
SELECT n FROM t;

其中，RECURSIVE表示遞歸，Oracle 和 SQL Server 中不需要該關鍵字。

遞歸 CTE 包含兩部分，UNION ALL 上面的查詢語句用于生成初始化數(shù)據(jù)；下面的查詢語句用于遞歸，引用了它自身（ t ）。

運行初始化語句，生成數(shù)字 1；
第 1 次運行遞歸部分，此時 n 等于 1，返回數(shù)字 2（ n+1 ）；
第 2 次運行遞歸部分，此時 n 等于 2，返回數(shù)字 3（ n+1 ）；
第 9 次運行遞歸部分，此時 n 等于 9，返回數(shù)字 10（ n+1 ）；
第 10 次運行遞歸部分，此時 n 等于 10；由于查詢不滿足條件（ WHERE n < 10 ），不返回任何結(jié)果，并且遞歸結(jié)束；
最后的查詢語句返回 t 中的全部數(shù)據(jù)，也就是一個 1 到 10 的數(shù)字序列。
1
2
3
4
5
6

只要是具有一定規(guī)律的數(shù)字序列都可以通過遞歸 CTE 生成，例如斐波那契數(shù)列。
遍歷組織結(jié)構圖

在公司的組織結(jié)構中，存在上下級的管理關系，如下圖所示。

示例表和數(shù)據(jù)點此下載。如果我們想要知道某個員工從上至下的各級領導，可以使用遞歸 CTE 實現(xiàn)：

WITH RECURSIVE employee_path (emp_id, emp_name, path) AS
(
SELECT emp_id, emp_name, CAST(emp_name AS CHAR(1000)) AS path
FROM employee
WHERE manager IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, CAST(CONCAT(ep.path, ‘->’, e.emp_name) AS CHAR(1000))
FROM employee_path ep
JOIN employee e ON ep.emp_id = e.manager
)
SELECT * FROM employee_path WHERE emp_name = ‘黃忠’;

 5|黃忠    |劉備->諸葛亮->黃忠|
1

上面是 MySQL 中的語法。在 Oracle 以及 SQL Server 中需要將 CHAR(1000) 改為 VARCHAR(1000)，同時省略 RECURSIVE 關鍵字；在 PostgreSQL 中需要將 CAST 函數(shù)里的 CHAR(1000) 改為 VARCHAR(1000)；SQLite 沒有提供 CONCAT 函數(shù)，使用連接操作符（||）即可。

其中，初始化查詢用于查找沒有 manager 的員工，也就是最上級的領導；遞歸查詢通過將員工的 manager 和上級員工的 emp_id 進行關聯(lián)，獲取上下級管理關系；遞歸結(jié)束的條件就是沒有找到任何數(shù)據(jù)。當然，我們也可以從下級往上級進行遍歷。

其他具有這種層級關系的數(shù)據(jù)包括多層菜單、博客文章中的評論等。
查找地鐵換乘線路

地鐵、公交、航班等，包括社交網(wǎng)站上的關注，都是一種有向圖數(shù)據(jù)結(jié)構。我們通常需要查找某一站點到另一站點的最短路徑，利用遞歸 CTE 可以實現(xiàn)這類需求。示例數(shù)據(jù)中目前只有地鐵 1 號線和 2 號線的數(shù)據(jù)，但是足夠我們進行演示。

以下語句用于查找“王府井”到“積水潭”的換乘路線，使用 PostgreSQL 數(shù)據(jù)庫實現(xiàn)：

WITH RECURSIVE paths (start_station, stop_station, stops, path) AS (
SELECT station_name, next_station, 1, ARRAY[station_name::text, next_station::text]
FROM bj_subway WHERE station_name = ‘王府井’
UNION ALL
SELECT p.start_station, e.next_station, stops + 1, p.path || ARRAY[e.next_station::text]
FROM paths p
JOIN bj_subway e
ON p.stop_station = e.station_name AND NOT e.next_station = ANY(p.path)
)
SELECT * FROM paths WHERE stop_station =‘積水潭’;

查詢結(jié)果顯示有 4 條路線，如果選擇最短路線就是第一條。其中的 path 字段是個數(shù)組，用于存儲走過的站點；最后的 NOT e.next_station = ANY(p.path) 條件用于避免反復經(jīng)過同一個站點，因為地鐵線路是一個雙向圖。

我們還可以進一步計算換乘次數(shù)，實現(xiàn)最少換乘路線；如果在表中增加一些字段，記錄每兩個站點之間的時間和換乘時間，還可以計算最快捷路線。

其他數(shù)據(jù)庫沒有提供數(shù)組類型，但是可以使用其他方法實現(xiàn)，以下是 MySQL 中的實現(xiàn)：

WITH RECURSIVE paths (start_station, stop_station, stops, path) AS (
SELECT station_name, next_station, 1, CAST(CONCAT(station_name , ‘,’, next_station) AS CHAR(1000))
FROM bj_subway WHERE station_name = ‘王府井’
UNION ALL
SELECT p.start_station, e.next_station, stops + 1, CONCAT_WS(’,’, p.path, e.next_station)
FROM paths p
JOIN bj_subway e
ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
)
SELECT * FROM paths WHERE stop_station =‘積水潭’;

我們使用了逗號分隔符的字符串模擬數(shù)組的效果，這種方法也適用于其他數(shù)據(jù)庫。你用哪種數(shù)據(jù)庫，有沒有提供這種功能？