傳統 BBS 樹狀結構常用遞歸查詢,但會產生大量 SQL 查詢,效能不佳。本文介紹「中值排序基數法」,透過單一排序欄位 ordernum 配合深度 deep,以單次查詢即可完整呈現樹狀結構。
在資料表新增三個冗餘欄位:
rootid:根文章 ID (0 表示根文章)deep:回覆深度 (0 為根文章)ordernum (float):排序基數,關鍵在於「取中值」| id | rootid | deep | ordernum | 動作 |
|---|---|---|---|---|
| 1 | 0 | 0 | 0 | 根文章 |
| 2 | 1 | 1 | 64 | 第一則回覆,基數 = 2^n |
| 3 | 1 | 1 | 32 | 回覆第 1 篇,取 (0+64)/2 = 32 |
| 4 | 1 | 2 | 48 | 回覆第 3 篇,取 (32+64)/2 = 48 |
| 5 | 1 | 3 | 56 | 回覆第 4 篇,取 (48+64)/2 = 56 |
| 6 | 1 | 2 | 40 | 回覆第 3 篇,取 (32+48)/2 = 40 |
依 ordernum 遞增排序,配合 deep 即可呈現:
1
3
6
4
5
2
ordernum = 0。ordernum = 2^n (如 65536)。ordernum = 最後一則的 ordernum + 2^n。ordernum = (前一則 ordernum + 後一則 ordernum) / 2。找出下一個 deep ≤ 目標文章 deep 的文章,刪除兩者 ordernum 之間 (不含邊界) 的所有文章。
例:刪除文章 3 (ordernum=32, deep=1) 及其子樹。下一個 deep≤1 為文章 2 (ordernum=64)。刪除 ordernum 在 32~64 之間的文章 (3,6,4,5)。
SELECT * FROM forum
ORDER BY rootid + id - SIGN(rootid)*id DESC, ordernum;
配合 deep 欄位即可輸出樹狀縮排。
CREATE PROCEDURE [add] @keyid int, @message varchar(50) OUTPUT
AS
IF (@keyid = 0)
INSERT INTO forum (rootid, deep, ordernum, ...) VALUES (0, 0, 0, ...);
ELSE
BEGIN
DECLARE @rootid int, @id int, @deep int, @begnum float, @endnum float, @ordernum float;
SELECT @rootid=rootid, @id=id, @begnum=ordernum, @deep=deep FROM forum WHERE id=@keyid;
IF (@id = 0)
BEGIN
SELECT @message = '要回覆的文章已被刪除!';
RETURN;
END
-- 後續計算 @endnum 並取中值邏輯...
END