CREATE TABLE Area ([id] [int] NOT NULL,[name] [nvarchar] (50) NULL,[parent_id] [int] NULL,[type] [int] NULL );
comment_id | parent_id | author | comment |
1 | 0 | 小明 | 我不大认同这个观点 |
2 | 1 | 小张 | 我也不认同 |
3 | 2 | 小红 | 我同意楼上 |
4 | 1 | 小全 | 你为什么不认同呢 |
5 | 4 | 小明 | 我以前遇到过这情况 |
6 | 5 | 小张 | 那也不代表你所说是对的 |
7 | 5 | 小新 | 这个视情况而定吧 |
SELECT c1.*, c2.* FROM comments c1 LEFT OUTER JOIN comments c2 ON c2.parent_id = c1.comment_id;
comment_id | path | author | comment |
1 | 1 | 小明 | 我不大认同这个观点 |
2 | 1/2 | 小张 | 我也不认同 |
3 | 1/2/3 | 小红 | 我同意楼上 |
4 | 1/4 | 小全 | 你为什么不认同呢 |
5 | 1/4/5 | 小明 | 我以前遇到过这情况 |
6 | 1/4/5/6 | 小张 | 那也不代表你所说是对的 |
7 | 1/4/5/7 | 小新 | 这个视情况而定吧 |
SELECT * FROM comments AS c WHERE '1/4/5/7' LIKE c.path || '%' ;
SELECT * FROM comemnts AS c WHERE c.path LIKE '1/4' || '%' ;
comment_id | nsleft | nsright | author | comment |
1 | 1 | 14 | 小明 | 我不大认同这个观点 |
2 | 2 | 5 | 小张 | 我也不认同 |
3 | 3 | 4 | 小红 | 我同意楼上 |
4 | 6 | 13 | 小全 | 你为什么不认同呢 |
5 | 7 | 12 | 小明 | 我以前遇到过这情况 |
6 | 8 | 9 | 小张 | 那也不代表你所说是对的 |
7 | 10 | 11 | 小新 | 这个视情况而定吧 |
SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c2.nsleft BETWEEN c1.nsleftAND c1.nsright WHERE c1.comment_id = 4;
SELECT c2.* FROM comments AS c1 JOIN comments AS c2 ON c1.nsleft BETWEEN c2.nsleftAND c2.nsright WHERE c1.comment_id = 6;
SELECT parent.* FROM comments AS c JOIN comments AS parent ON c.nsleft BETWEEN parent.nsleft AND parent.nsrightLEFT OUTER JOIN comments AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsrightAND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6AND in_between.comment_id IS NULL;
祖先 | 后代 | 祖先 | 后代 | 祖先 | 后代 | 祖先 | 后代 |
1 | 1 | 1 | 7 | 4 | 6 | 7 | 7 |
1 | 2 | 2 | 2 | 4 | 7 | ||
1 | 3 | 2 | 3 | 5 | 5 | ||
1 | 4 | 3 | 3 | 5 | 6 | ||
1 | 5 | 4 | 4 | 5 | 7 | ||
1 | 6 | 4 | 5 | 6 | 6 |
INSERT INTO treepaths (ancestor, descendant)SELECT t.ancestor, 8FROM treepaths AS tWHERE t.descendant = 6UNION ALL SELECT 8, 8;
DELETE FROM treepaths WHERE descendant = 7;
方案 | 表数量 | 查询子 | 查询树 | 插入 | 删除 | 引用完整性 |
邻接表 | 1 | 简单 | 困难 | 简单 | 简单 | 是 |
枚举路径 | 1 | 简单 | 简单 | 简单 | 简单 | 否 |
嵌套集 | 1 | 困难 | 简单 | 困难 | 困难 | 否 |
闭包表 | 2 | 简单 | 简单 | 简单 | 简单 | 是 |
以上是数据库表设计-邻接表、路径枚举、嵌套集、闭包表的详细内容。更多信息请关注PHP中文网其他相关文章!