如何找到在SQL Server表的表列的所有的孩子呢? [英] How to find all child of a table column in sql server table?
本文介绍了如何找到在SQL Server表的表列的所有的孩子呢?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个SQL数据库表
I have a sql database table
CREATE TABLE [dbo].[LedgerGroups](
[GroupName] [varchar](50) NOT NULL primary key,
[GroupParent] [varchar](50) NULL
)
,表中的记录是
The records in the table are
-----组名------ --- GroupParent ---
的
Bangalore Customers Karnataka Customers
Chennai Customers Tamilnadu Customers
Customers NULL
Ernakulam Customers Kerala Customers
Hubli Customers Karnataka Customers
Karnataka Customers Customers
Kerala Customers Customers
Kollam Customers Kerala Customers
Nagercoil Customers Tamilnadu Customers
Suppliers NULL
Tamilnadu Customers Customers
Tirunelveli Customers Tamilnadu Customers
我怎样才能查询该表,这样我可以得到一个特定值的所有子。对于恩,如果我给客户
这应该返回其父母为客户及其孩子的童车等所有行。
How can i query the table so that i can get all the child of a particular value. For ex, if i give Customers
it should return all rows whose parent is Customers and childs of its childs and so on..
推荐答案
这应该做的:
DECLARE @Group VARCHAR(50)
SET @Group = 'Karnataka Customers'
;WITH CTE AS
(
SELECT *
FROM [dbo].[LedgerGroups]
WHERE GroupParent = @Group
UNION ALL
SELECT B.*
FROM CTE A
INNER JOIN [dbo].[LedgerGroups] B
ON A.GroupName = B.GroupPArent
)
SELECT *
FROM CTE
OPTION(MAXRECURSION 0);
在这里是一个工作sqlfiddle 给你试用。
这篇关于如何找到在SQL Server表的表列的所有的孩子呢?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文