棘手的递归 MySQL 查询 [英] Tricky Recursive MySQL query

问题描述

我有一个有 2 列的表，usersID 和他们的 siblingID

I have a table that has 2 column, usersID and their siblingID

找到给定用户的所有兄弟姐妹的最佳方法是什么?

What is the best way to find all the siblings of a given user?

问题很复杂.这是一个例子.

The issue is complex. Here is an example.

用户 1 有 5 个兄弟姐妹(2,3,4,5,6)

User 1 has 5 siblings(2,3,4,5,6)

桌子看起来像这样

userID|siblingID
1     | 2
1     | 3
6     | 5
5     | 3
3     | 1
4     | 6

推荐答案

ANSI SQL:

with recursive tree (userid, siblingid) as
(
   select userid, 
          siblingid
   from users
   where userId = 1
   union all 
   select c.userid,
          c.siblingid
   from users c
     join tree p on p.userid c.siblingId
)
select *
from tree;

对于 Oracle 11.2 和 SQL Server - 显然没有仔细查看 ANSI 规范 - 您需要删除 recursive 关键字(根据标准这是强制性的)

For Oracle 11.2 and SQL Server - who apparently didn't look at the ANSI specs closely - you need to remove the recursive keyword (it is mandatory according to the standard)

这篇关于棘手的递归 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！