Sql数据库中的推导子父记录. [英] Reterive child parent records from Sql Database.

问题描述

大家好，

我正在使用SQL Server 2008的asp.Net项目中工作.
我有一个表"table1".
表名称:table1
列:

Hello Every One,

I am working in a asp.Net project in which i am using Sql server 2008.
I have a table "table1".
table name : table1
columns:

empId(Int) empname(varchar)     parentid(int)
1           raj kumar            -1
2           sunil rawat           1 
3           prem kumar            1
4           sunny sohni           2
5           laura dutta           3
6           honey                 3

上面是我的桌子.现在我想要一个父母ID为1的员工的记录.
但我也希望，如果像3这样的1个Empid的孩子也有孩子，那么应该将其取回.任何人都知道将为此使用什么sql查询.

请帮助我..

above is my table. now i want a record of those employee whose parent id is 1.
but i also want that if child of 1 empid like 3 has also child then it should be retrieve. any one know what sql query will be use for this.

please help me..

推荐答案

这取决于您要如何存储数据.但是例如，如果您需要父母和孩子在同一行上，则可以尝试类似

This depends on how you want to have your data. But for example if you need parent and child on the same row, you could try something like

SELECT t1.EmpId, t1.EmpName, t2.EmpId, t2.EmpName
FROM table1 t1 left outer join table2 t2
     on t1.EmpId = t2.ParentId

加法，递归示例

Addition, recursive example

WITH Employees(ParentId, EmpID, EmpName, Level) AS (
    SELECT t1.ParentId, t1.EmpID, t1.EmpName, 0 AS Level
    FROM table1 t1
    WHERE ParentId = -1
    UNION ALL
    SELECT t2.ParentId, t2.EmpID, t2.EmpName, Level + 1
    FROM table1 t2 inner join Employees e
        ON e.EmpId = t2.ParentId
)
SELECT ParentId, EmpID, EmpName, Level
FROM Employees

这篇关于Sql数据库中的推导子父记录.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！