递归查询
我有一个表，其中包含以下字段
- Supervisorid
- Empid
这就像一个推荐计划。一个人可以引用他下面的 3 个家伙，即，3 指的是三个人，即 4 5 8 类似地 4 指的是 9 10 和 11 同样 8 指的是 12, 13 它是这样的..
我想要一个查询来获取主管下的所有 EmpId 3
I have a table which contains the following fields
- Supervisorid
- Empid
This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..
I want a query to get all EmpId under Supervisor 3
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是否希望我们为您编写解决方案，或者解释一下如何构建递归查询？
有关如何构建它们的示例位于 http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad/samples /clp/s-flt-db2.htm。
IBM DB2 红皮书有一整章介绍 SQL 递归。
要点是通常涉及以下步骤：
定义“种子”。 SELECT SUPID, EMPID, 1 AS LVL FROM EMP WHERE SUPID = 3;
您为其指定一个名称。使用 SRC AS <您的种子>
您可以使用分配的名称定义进入“下一个级别”的方式，从种子开始。 SELECT SRC.SUPID, F.EMPID, SRC.LVL+1 FROM SRC, EMP WHERE SRC.EMPID=EMP.SUPID
将两者组合在一起（在WITH子句中）WITH SRC ASUNION ALL <此处的另一个 SELECT>
（可选）您定义要选择的列。 SELECT EMPID, LVL FROM SRC。
Do you want us to write the solution for you, or explain a bit how recursive queries can be built up ?
An example of how they are built up is on http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad/samples/clp/s-flt-db2.htm.
The IBM DB2 redbook has an entire chapter on SQL recursion.
The gist is that the following steps are generally involved:
you define a "seed". SELECT SUPID, EMPID, 1 AS LVL FROM EMP WHERE SUPID = 3;
you assign to this a name. WITH SRC AS <your seed here>
you define the way to go to the 'next level', starting from the seed, using the assigned name. SELECT SRC.SUPID, F.EMPID, SRC.LVL+1 FROM SRC, EMP WHERE SRC.EMPID=EMP.SUPID
you combine the two together (inside the WITH clause) WITH SRC AS <your seed here> UNION ALL <the other SELECT here>
(optionally) you define which columns to select. SELECT EMPID, LVL FROM SRC.