不带递归的Sql递归
我有四张桌子
create table entities{
integer id;
string name;
}
create table users{
integer id;//fk to entities
string email;
}
create table groups{
integer id;//fk to entities
}
create table group_members{
integer group_id; //fk to group
integer entity_id;//fk to entity
}我想要执行一个查询，直接或间接返回用户所属的所有组。显而易见的解决方案是在应用程序级别进行递归。我想知道我可以对我的数据模型进行哪些更改，以减少数据库访问，从而获得更好的性能。
回答 6
Stack Overflow用户
发布于 2009-08-24 16:17:37
在Oracle中
SELECT group_id
FROM group_members
START WITH
entity_id = :user_id
CONNECT BY
entity_id = PRIOR group_id在SQL Server中
WITH q AS
(
SELECT group_id, entity_id
FROM group_members
WHERE entity_id = @user_id
UNION ALL
SELECT gm.group_id, gm.entity_id
FROM group_members gm
JOIN q
ON gm.entity_id = q.group_id
)
SELECT group_id
FROM q在PostgreSQL 8.4中
WITH RECURSIVE
q AS
(
SELECT group_id, entity_id
FROM group_members
WHERE entity_id = @user_id
UNION ALL
SELECT gm.group_id, gm.entity_id
FROM group_members gm
JOIN q
ON gm.entity_id = q.group_id
)
SELECT group_id
FROM q在PostgreSQL 8.3及以下版本中：
CREATE OR REPLACE FUNCTION fn_group_members(INT)
RETURNS SETOF group_members
AS
$$
SELECT group_members
FROM group_members
WHERE entity_id = $1
UNION ALL
SELECT fn_group_members(group_members.group_id)
FROM group_members
WHERE entity_id = $1;
$$
LANGUAGE 'sql';
SELECT group_id
FROM group_members(:myuser) gmStack Overflow用户
发布于 2009-08-24 16:49:46
有一些方法可以避免树状层次结构查询中的递归(与人们在这里所说的相反)。
我使用最多的是Nested Sets。
然而，对于所有的生活和技术决策，都需要进行权衡。嵌套集的更新速度通常较慢，但查询速度要快得多。有一些聪明而复杂的方法可以提高层次结构的更新速度，但还有另一个权衡:性能与代码复杂性。
嵌套集合的一个简单示例...
树状视图：
-Electronics
|
|-Televisions
| |
| |-Tube
| |-LCD
| |-Plasma
|
|-Portable Electronics
|
|-MP3 Players
| |
| |-Flash
|
|-CD Players
|-2 Way Radios嵌套集合表示
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+您需要阅读article I linked才能完全理解这一点，但我将尝试给出一个简短的解释。
如果(子项目的"lft“(左)值大于父项目的"ltf”值)并且(子项目的"rgt“值小于父项目的"rgt”值)，则项目是另一个项目的成员。
"MP3“是”Flash PLAYERS“、"Portable Electronics”和"Electronics“的一员。
或者，conversley，“便携电子”的成员是：
Players
- Flash
- CD播放器
- MP3
- 双向无线电
Joe Celko有一整本关于“SQL中的树和层次结构”的书。有比你想象的更多的选择，但需要做出很多权衡。
注意:永远不要说不能做的事情，一些mofo会在can中显示给你看。
Stack Overflow用户
发布于 2009-08-24 16:09:33
你能澄清实体和用户之间的区别吗？否则，您的表看起来是正常的。您假设在组和实体之间存在多对多关系。
在任何情况下，对于标准SQL，请使用以下查询：
SELECT name, group_id
FROM entities JOIN group_members ON entities.id = group_members.entity_id;这将为您提供一个名称和group_ids的列表，每行一对。如果某个实体是多个组的成员，则会多次列出该实体。
如果您想知道为什么没有连接到groups表，那是因为groups表中没有已经存在于group_members表中的数据。比方说，如果您在groups表中包含了一个组名称，并且希望显示该组名称，那么您也必须加入组。
某些SQL变体具有与报告相关的命令。它们允许您将多个组作为单个实体列在同一行上。但它不是标准的，也不会在所有平台上都能工作。
https://stackoverflow.com/questions/1323245
复制相似问题
腾讯云开发者
