我有一个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 给你试用。