使用自引用表创建嵌套网格

I have a table with following schema:

我有一个包含以下架构的表:

SELECT [ParkingCardId],
       [ParentParkingCardId],
       [CompanyId],
       [DateRequested],
       [StaffNo],
       [Name],
       [Section],
       [JobTitle],
       [Position],
       [Telephone],
       [Mobile],
       [Fax],
       [POBox],
       [Email],
       [Nationality],
       [Gender],
       [ShiftType],
       [Amount],
       [PassIssueDate],
       [PassExpiryDate]
FROM [DCAServices].[dbo].[ParkingCards];

The first two columns are key here: ParkingCardId is the PK and ParentParkingCardId, if not NULL, is pointing to another ParkingCardId in the same table and is, itself, a renew of a lost card case.

前两列是关键点:ParkingCardId是PK和ParentParkingCardId,如果不是NULL,则指向同一个表中的另一个ParkingCardId,它本身就是一个丢失的卡片盒的更新。

I want to display this information as hierarchical in Grid on MVC (Kendo) but my DAL is pure Entity Framework based and I have not used any Stored Procedures so far. I know it may not be easy to have a LINQ query to transform this data.

我希望在MVC(Kendo)的Grid中将此信息显示为层次结构,但我的DAL是基于纯实体框架的,到目前为止我还没有使用任何存储过程。我知道有一个LINQ查询来转换这些数据可能并不容易。

There is also a possibility that there may be generations of parents. A staff might be on his eights renewal. So the parent row on the grid may expand to show one child that may be a parent itself and so on.

也有可能有几代父母。一名工作人员可能正在重建他的职位。因此,网格上的父行可以展开以显示一个可能是父本身的子项,依此类推。

I'm currently looking at search result that comes up against query like "Parent-Child relation in the same table"

我目前正在查看针对查询的搜索结果,例如“同一个表中的父子关系”

1 个解决方案

#1

This SQL will return hierarchical result with Level of current item

此SQL将返回具有当前项级别的分层结果

WITH cte as
(
  SELECT i.[ParkingCardId], i.[ParentParkingCardId], i.[CompanyId], i.[DateRequested], i.[StaffNo], i.[Name], i.[Section], i.[JobTitle], i.[Position], 
  i.[Telephone], i.[Mobile], i.[Fax], i.[POBox], i.[Email], i.[Nationality], i.[Gender], i.[ShiftType], i.[Amount], i.[PassIssueDate], i.[PassExpiryDate], 0 AS [Level]
  FROM [DCAServices].[dbo].[ParkingCards] i
  WHERE i.[ParentParkingCardId] is null

  UNION ALL

  SELECT i1.[ParkingCardId], i1.[ParentParkingCardId], i1.[CompanyId], i1.[DateRequested], i1.[StaffNo], i1.[Name], i1.[Section], i1.[JobTitle], i1.[Position], 
  i1.[Telephone], i1.[Mobile], i1.[Fax], i1.[POBox], i1.[Email], i1.[Nationality], i1.[Gender], i1.[ShiftType], i1.[Amount], i1.[PassIssueDate], i1.[PassExpiryDate], [Level] + 1
  FROM [DCAServices].[dbo].[ParkingCards] i1
  INNER JOIN cte
  ON cte.[ParkingCardId] = i1.[ParentParkingCardId]
 )
SELECT * From cte
ORDER BY [Level]

Check this link Common Table Expressions

检查此链接公用表表达式

#1