SQL Server CTE层次结构问题 [英] SQL Server CTE hierarchy issue

查看：57
本文介绍了SQL Server CTE层次结构问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个应用程序可以提供多级权限和角色



我有这个层次结构：





国家



..区域



.. ...城市



.........协会



... ......................中心



...........学校



............................... Class





此层次结构我将其命名为 EntityLevels 

  | ID |姓名| ParentID | 
 | ---- | ------------- || ---------- | 
 | 1 |国家（地区）|空| 
 | 2 |地区| 1 | 
 | 3 |城市| 2 | 
 | 4 |协会| 3 | 
 | 5 |中心| 4 | 
 | 6 |学校| 5 | 
 | 7 |类6 |





我也有一个Groups Table，表示Jobs 

  | ID |姓名|实体级别| 
 | ---- | -------------------- | -------------- | 
 | 1 | CountryAdmins | 1 | 
 | 2 |地区主管| 2 |





用户表如下

  | ID |姓名| 
 | ---- | ------- | 
 | 1 |用户1 | 
 | 2 |用户2 |





现在我有一个UserJobs表或UserGroups 

  | ID |用户名| GroupdID | EntityID | 
 | ---------------- | ---------- | ---------- | 
 | 1 |用户1 | 1 | 1 | 
 | 2 |用户2 | 2 | 2 | 
 | 3 |用户3 | 4 | 38 |





现在的问题是我如何获得每个用户以及他的职责取决于其级别



对于eaxmple：



user1必须具有所有角色，并且允许查看他级别以下的所有用户，因为他位于组（1）中，而组1位于国家级别的EntityLevel（1）上。



我已经尝试做类似的事情，但是没有按预期工作，只能给我一个根，而该根下没有其他孩子

  ;通过MyCTE AS（
选择T1.ID，UserId，0 AS TreeLevel，CAST（T1.ID AS VARCHAR（255））AS从UserJobs T1中的TreePath 
内部联接EntityLevel el on t1.GroupId = el .Id WHERE EL.ParentID为NULL 
 
 UNION ALL 
 
 SELECT T2.ID，T2.UserId，TreeLevel + 1，CAST（TreePath +'。'+ CAST（T2 .ID AS VARCHAR（255））AS VARCHAR（255））TreePath 
来自UserJobs T2 
内部联接T2上的EntityLevel el.GroupId = el。 ID 
内部联接
 MyCTE itms在itms.ID = EL.ParentID 
）
 
 SELECT ID，TreeLevel，TreePath 
 FROM MyCTE 
 ORDER BY TreePath;





用于模式和数据的脚本



dbfiddle





脚本和数据版本＃2 

 创建表[dbo]。[关联]（
 [ID] [int]不为空，
 [名称] [nvarchar]（50）为空，
 [CityID] [ int] NULL，
约束[PK_Assocation]主键聚集
（
 [ID] ASC 
），其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON ，ALLOW_PAGE_LOCKS = ON）开启[PRIMARY] 
）开启[PRIMARY] 
 
 GO 
 / ******对象：Table [dbo]。[Center]脚本日期：2017-04-04 3:47:05 PM ****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
创建表[dbo]。[Center]（
 [ID] [int] NOT NULL，
 [名称] [nvarchar]（50）NULL，
 [AssociationID] [int] NULL，
约束[PK_Center]主键簇
（
 [ ID] ASC 
），其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
）ON [PRIMARY] 
 
 GO 
 / ******对象：表[dbo]。[City]脚本日期：2017-04-04 3:47:05 PM ****** / 
 SET ANSI_NULLS在
上运行
在
上设置QUOTED_IDENTIFIER在
创建表[dbo]。[城市]（
 [ID] [int]不为空，
 [名称] [nvarchar]（50）NULL，
 [RegionID] [int] NULL，
约束[PK_City]主键簇
（
 [ID] ASC 
 ）（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = O FF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）开启[主] 
）开启[主] 
 
 GO 
 / ******对象：表[dbo]。 [班级]脚本日期：2017-04-04 3:47:05 PM ****** / 
在
上设置ANSI_NULLS转到
在
上设置QUOTED_IDENTIFIER转到
创建表[dbo]。[类]（
 [ID] [int]不为空，
 [名称] [nvarchar]（50）为空，
 [SchoolID] [int] NULL，
约束[PK_Class]主键聚集
（
 [ID] ASC 
）其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_S = ON）ON [PRIMARY] 
）ON [PRIMARY] 
 
 GO 
 / ******对象：表[dbo]。[Country]脚本日期：2017年-04-04 3:47:05 PM ****** / 
在
上设置ANSI_NULLS GO 
在
上设置QUOTED_IDENTIFIER到
创建表[dbo] 。[Country]（
 [ID] [int]非空，
 [名称] [nvarchar]（50）空，
约束[PK_Country]主键群集
（
 [ID] ASC 
）与（PAD_INDEX = OFF ，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）开启[主] 
）开启[主] 
 
转到
 / ******对象：表[dbo]。[EntityLevel]脚本日期：2017-04-04 3:47:05 PM ****** / 
设置ANSI_NULLS ON 
转到
设置QUOTED_IDENTIFIER ON 
 GO 
创建表[dbo]。[EntityLevel]（
 [ID] [int]不为空，
 [名称] [nvarchar]（50）NULL，
 [ParentID] [int] NULL，
约束[PK_Table_1]主键聚集
（
 [ID] ASC 
），其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）打开[PRIMARY] 
）打开[PRIMARY] 
 
 GO 
 / ******对象：表[dbo]。 [小组]脚本日期：2017-04-04 3:47:05 PM ****** / 
在
上设置ANSI_NULLS GO 
在
上设置QUOTED_IDENTIFIER到
创建表[dbo]。[组]（
 [ID] [int]非空，
 [名称] [nvarchar]（50）空，
 [EntityLevelID] [int] NULL，
 CO NSTRAINT [PK_Group]主键群集
（
 [ID] ASC 
），其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [主] 
）ON [PRIMARY] 
 
 GO 
 / ******对象：表[dbo]。[地区]脚本日期：2017-04-04 3： 47:05 PM ****** / 
将ANSI_NULLS设置为
 GO 
将QUOTED_IDENTIFIER设置为
 GO 
创建表[dbo]。[区域]（
 [ID] [int]非空，
 [名称] [nvarchar]（50）NULL，
 [CountryID] [int]空，
约束[PK_Region]主键
（
 [ID] ASC 
）与（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [主] 
）ON [主要] 
 
 GO 
 / ******对象：Table [dbo]。[学校]脚本日期：2017-04-04 3:47:05 PM *** *** / 
将ANSI_NULLS设置为
 GO 
将QUOTED_IDENTIFIER设置为
 GO 
创建表[dbo]。[学校]（
 [ID] [ int] NOT N ULL，
 [名称] [nvarchar]（50）NULL，
 [CenterID] [int] NULL，
约束[PK_School]主键群集
（
 [ ID] ASC 
），其中（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
）ON [PRIMARY] 
 
 GO 
 / ******对象：表[dbo]。[用户]脚本日期：2017-04-04 3:47:05 PM ****** / 
 SET ANSI_NULLS在
上运行
在
上设置QUOTED_IDENTIFIER在
上创建表[dbo]。[用户]（
 [ID] [int]不为NULL，
 [名称] [nvarchar]（50）NULL，
 [移动] [varchar]（50）NULL，
约束[PK_User]主键群集
（
 [ID] ASC $ b（b $ b））（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [主] 
）ON [主] 
 
 GO 
 / ******对象：表[dbo]。[UserJobs]脚本日期：2017-04-04 3:47:05 PM ****** / 
在$ b $上设置ANSI_NULLS b转到
设置报价ED_IDENTIFIER ON 
转到
创建表[dbo]。[UserJobs]（
 [ID] [int]不为空，
 [UserID] [int]不为空，
 [GroupID] [int]非空，
 [EntityID] [int]非空，
约束[PK_UserJobs]主键群集
（
 [ID] ASC，
 [UserID] ASC，
 [GroupID] ASC，
 [EntityID] ASC 
）与（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON ）在[主] 
上）在[主] 
 
 GO 
插入[dbo]。[关联]（[ID]，[名称]，[CityID]）值（ 1，N'KH'，1）
 GO 
插入[dbo]。[关联]（[ID]，[名称]，[CityID]）值（2，N'mkh_ass'，2） 
 GO 
插入[dbo]。[Center]（[ID]，[Name]，[AssociationID]）值（1，N'NorthCenter'，1）
 GO 
 INSERT [dbo]。[Center]（[ID]，[Name]，[AssociationID]）值（2，N'SouthCenter'，1）
 GO 
 INSERT [dbo]。[城市]（ [ID]，[Name]，[RegionID]）值（1，N'Jeddah'，1）
 GO 
插入[dbo]。[城市]（ [ID]，[Name]，[RegionID]）值（2，N'MakkahCiry'，1）
 GO 
插入[dbo]。[Class]（[ID]，[Name]，[ SchoolID]）值（1，N'Class1'，1）
 GO 
插入[dbo]。[Class]（[ID]，[Name]，[SchoolID]）VALUES（2，N' Class2'，1）
 GO 
插入[dbo]。[Class]（[ID]，[Name]，[SchoolID]）值（3，N'class3'，2）
 GO 
插入[dbo]。[类]（[ID]，[名称]，[SchoolID]）值（4，N'class4'，2）
 GO 
插入[dbo] 。[Country]（[ID]，[Name]）值（1，N'KSA'）
 GO 
插入[dbo]。[Country]（[ID]，[Name]）值（ 2，N'UAE'）
 GO 
插入[dbo]。[EntityLevel]（[ID]，[Name]，[ParentID]）值（1，N'Country'，NULL）
 GO 
 INSERT [dbo]。[EntityLevel]（[ID]，[Name]，[ParentID]）值（2，N'Region'，1）
 GO 
 INSERT [ dbo]。[EntityLevel]（[ID]，[Name]，[ParentID]）值（3，N'City'，2）
 GO 
插入[dbo]。[EntityLevel]（[ID ]，[Name]，[ParentID]）值（4，N'Association'，3）
 GO 
插入[dbo]。[EntityLevel]（[ID]，[Name]，[ParentID] ）值（5，N'Center '，4）
 GO 
插入[dbo]。[EntityLevel]（[ID]，[Name]，[ParentID]）值（6，N'School'，5）
 GO 
 INSERT [dbo]。[EntityLevel]（[ID]，[Name]，[ParentID]）值（7，N'Class'，6）
 GO 
 INSERT [dbo]。 [Group]（[ID]，[Name]，[EntityLevelID]）值（1，N'Country Manager'，1）
 GO 
插入[dbo]。[Group]（[ID]， [名称]，[EntityLevelID]）值（2，N'Region Manager'，2）
 GO 
插入[dbo]。[组]（[ID]，[名称]，[EntityLevelID]）值（3，N'City Manager'，3）
 GO 
插入[dbo]。[Group]（[ID]，[Name]，[EntityLevelID]）VALUES（4，N'Association Manager '，4）
 GO 
插入[dbo]。[Group]（[ID]，[Name]，[EntityLevelID]）值（5，N'Center Manager'，5）
 GO 
插入[dbo]。[组]（[ID]，[名称]，[EntityLevelID]）值（6，N'School Manager'，6）
 GO 
插入[dbo ]。[Group]（[ID]，[Name]，[EntityLevelID]）值（7，N'Teacher'，7）
 GO 
插入[dbo]。[区域]（[ID] ，[名称]，[国家ID]）值（1，N'Makkah'，1）
 GO 
插入[dbo]。[区域]（[ID]，[Name]，[CountryID]）值（2，N'Riyadh'，1）
 GO 
插入[dbo]。[学校]（[ID]，[Name] ，[CenterID]）值（1，N'School1'，1）
 GO 
插入[dbo]。[学校]（[ID]，[名称]，[CenterID]）值（2， N'School2'，1）
 GO 
插入[dbo]。[学校]（[ID]，[名称]，[CenterID]）值（3，N'School3'，2）
 GO 
插入[dbo]。[用户]（[ID]，[名称]，[移动设备]）值（1，N'UserA'，N'000000'）
 GO 
插入[dbo]。[用户]（[ID]，[名称]，[移动]）值（2，N'UserB'，N'1111'）
 GO 
插入[dbo]。 [用户]（[ID]，[名称]，[移动电话]）值（3，N'UserC'，N'2222'）
 GO 
插入[dbo]。[User]（[ID ]，[名称]，[移动设备]）值（4，N'UserD'，N'3333'）
 GO 
插入[dbo]。[User]（[ID]，[Name]， [Mobile]）VALUES（5，N'UserE'，N'4444'）
 GO 
插入[dbo]。[User]（[ID]，[Name]，[Mobile]）VALUES（ 6，N'UserF'，N'5555'）
 GO 
插入[dbo]。[User]（[ID]，[Name]，[Mobile]）值（7，N'UserG' ，N'6548'）
 GO 
 INSERT [dbo]。[User]（[ID]，[Name]，[Mobile]）VALUE S（8，N'UserH'，NULL）
 GO 
插入[dbo]。[User]（[ID]，[Name]，[Mobile]）值（9，N'UserI'， NULL）
 GO 
插入[dbo]。[User]（[ID]，[Name]，[Mobile]）值（10，N'UserJ'，NULL）
 GO 
 INSERT [dbo]。[UserJobs]（[ID]，[UserID]，[GroupID]，[EntityID]）值（1、1、1、1）
 GO 
 INSERT [dbo] 。[UserJobs]（[ID]，[UserID]，[GroupID]，[EntityID]）值（2、2、2、1）
 GO 
插入[dbo]。[UserJobs]（[ ID]，[UserID]，[GroupID]，[EntityID]）值（3、3、3、1）
 GO 
插入[dbo]。[UserJobs]（[ID]，[UserID] ，[GroupID]，[EntityID]）值（4、4、4、1）
 GO 
插入[dbo]。[UserJobs]（[ID]，[UserID]，[GroupID]，[ EntityID]）值（5、5、5、1）
 GO 
插入[dbo]。[UserJobs]（[ID]，[UserID]，[GroupID]，[EntityID]）值（6 ，6，6，1）
 GO 
插入[dbo]。[UserJobs]（[ID]，[UserID]，[GroupID]，[EntityID]）值（7、7、7、1 ）
 GO 
插入[dbo]。[UserJobs]（[ID]，[UserID]，[GroupID]，[EntityID]）值（8、8、2、2）
 GO 
 INSERT [dbo]。[用户作业]（[ID]，[UserID]，[GroupID]，[EntityID]）值（9、9、3、2）
 GO 
插入[dbo]。[UserJobs]（[ID] ，[UserID]，[GroupID]，[EntityID]）值（10、10、4、2）
转到





有什么解决方案？

解决方案

恕我直言必须先构建整个实体树，然后将其用于与其他表进行联接。


看看您想要的结果，我不清楚实体和城市之间的关系是什么，区域，类别等。


显然，根据实体名称，我知道1 =国家/地区，2 =地区，依此类推，但是我找不到任何字段您的表架构，该表架构可获取以下信息以外的其他信息：

  CASE WHEN Entity.ID = 1（选择来自国家/地区的名称，其中ID = Entity.ID ）END 
，当Entity.ID = 2（从区域WHERE ID = Entity.ID中选择名称）END 
当Entity.ID = 3（从城市WHERE ID = Entity.ID中选择名称）END 
 ... 
 END作为EntityName

我建议您构建一个UDF或SP以获取实体的名称并使用


 ;与树为
（
 SELECT e1.ID ，e1.Name，e1.ParentID，[级别] = 1 
来自EntityLevel e1 
 WHERE e1.ParentID =（从UserJobs中选择EntityID，其中UserID = 1）
 UNION ALL 
选择e2.ID，e2.Name，e2.ParentID，[级别] =树。[级别] + 1 
从EntityLevel e2 
内部联接树
在e2.ParentID = tree.ID 
）
选择EntityLevelID，UserName，GroupID，GroupName，EntityID，EntityName 
从树t 
内部联接（SELECT gr.entitylevelid，
 us.Name UserName，
 gr.Name GroupName，
 el.Name为EntityName，
 gr.ID为GroupID，
 el.ID为EntityID 
 FROM userjobs uj 
 I NNER JOIN [group] gr 
 gr.id = uj.groupid 
 INNER JOIN实体级别el 
 ON el.id = gr.entitylevelid 
 INNER JOIN [用户]我们
 ON us.id = uj.userid）t1 
 ON t.ID = t1.EntityLevelID 
选项（MAXRECURSION 0）
; 
 
 GO 
  


 
 EntityLevelID |用户名| GroupID |组名| EntityID | EntityName 
 ------------：| ：------- | ------：| ：---------------- | -------：| ：---------- 
 2 |用户2 | 3 |区域管理员| 2 |区域
 2 |用户3 | 4 |地区主管| 2 |区域
 4 |用户4 | 5 |经理| 4 |协会
 5 | user5 | 6 |主管| 5 |中心
 7 |用户6 | 7 |老师| 7 |类


dbfiddle 此处


i have an application serve multilevel of permissions and roles

i have this HIERARCHY :

Country

....Region

........City

............Association

................Center

....................School

........................Class

this HIERARCHY i name it "EntityLevels"

| ID | Name        | ParentID |
|----|-------------|----------|
| 1  | Country     | Null     |
| 2  | Region      | 1        |
| 3  | City        | 2        |
| 4  | Association | 3        |
| 5  | Center      | 4        |
| 6  | School      | 5        |
| 7  | Class       | 6        |

i have also a Groups Table which means Jobs 

| ID | Name               | EntityLevels |
|----|--------------------|--------------|
| 1  | CountryAdmins      | 1            |
| 2  | Region Supervisors | 2            |

the user table is as following 

| ID | Name  |
|----|-------|
| 1  | User1 |
| 2  | User2 |

now i have a UserJobs Table or UserGroups

| ID | UserID | GroupdID | EntityID |
|----|--------|----------|----------|
| 1  | User1  | 1        | 1        |
| 2  | User2  | 2        | 2        |
| 3  | User3  | 4        | 38       |

now the problem is how i can get each user and his responsibilites depending on what it's under his level

for eaxmple :

user1 must have all the roles and permissoins to see all users under his level because he is in Group (1) and Group1 it resides on EntityLevel (1) which it's on the Country Level.

i've try to do something like that, but it's not working as Expected it's Only give me the root without any other child under that root

;WITH MyCTE AS (
  SELECT T1.ID, UserId, 0 AS TreeLevel, CAST(T1.ID AS VARCHAR(255)) AS TreePath FROM UserJobs T1
  inner join EntityLevel el on t1.GroupId = el.Id WHERE EL.ParentID IS NULL

  UNION ALL

  SELECT T2.ID, T2.UserId, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath
  FROM UserJobs T2
  inner join EntityLevel el on T2.GroupId = el.Id
  INNER JOIN
  MyCTE itms ON itms.ID = EL.ParentID
)

SELECT ID, TreeLevel, TreePath
FROM  MyCTE 
ORDER BY TreePath;

Script for Schema And DATA

dbfiddle here

CREATE TABLE [dbo].[Assocation](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CityID] [int] NULL,
 CONSTRAINT [PK_Assocation] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Center]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Center](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [AssociationID] [int] NULL,
 CONSTRAINT [PK_Center] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[City]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [RegionID] [int] NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Class]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [SchoolID] [int] NULL,
 CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Country]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[EntityLevel]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntityLevel](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [ParentID] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Group]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [EntityLevelID] [int] NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Region]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CountryID] [int] NULL,
 CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[School]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CenterID] [int] NULL,
 CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[User]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Mobile] [varchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[UserJobs]    Script Date: 2017-04-03 6:07:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserJobs](
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [GroupID] [int] NOT NULL,
    [EntityID] [int] NOT NULL,
 CONSTRAINT [PK_UserJobs] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [UserID] ASC,
    [GroupID] ASC,
    [EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (1, N'KH', 1)
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (2, N'mkh_ass', 2)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (1, N'NorthCenter', 1)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (2, N'SouthCenter', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (1, N'Jeddah', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (2, N'MakkahCiry', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (1, N'Class1-Ahmed', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (2, N'Class2-omar', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (3, N'class3_khaled', 2)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (4, N'class4_fahd', 2)
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (1, N'KSA')
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (2, N'UAE')
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (1, N'Country', NULL)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (2, N'Region', 1)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (3, N'City', 2)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (4, N'Association', 3)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (5, N'Center', 4)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (6, N'School', 5)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (7, N'Class', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (1, N'SA', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (2, N'country admin', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (3, N'region admin', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (4, N'region Supervisor', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (5, N'manager', 4)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (6, N'supervisor', 5)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (7, N'teacher', 7)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (1, N'Makkah', 1)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (2, N'Riyadh', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (1, N'School1', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (2, N'School2', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (3, N'School3', 2)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (1, N'Loai', N'000000')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (2, N'User1', N'1111')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (3, N'User2', N'2222')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (4, N'User3', N'3333')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (5, N'User4', N'4444')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (6, N'user5', N'5555')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (7, N'user6', N'6548')
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (1, 1, 1, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (2, 2, 2, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (3, 3, 3, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (4, 4, 4, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (5, 5, 5, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (6, 6, 6, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (7, 7, 7, 2)
GO

(EDIT) : The Expected Result will be :

SCRIPT AND DATA VERSION #2

CREATE TABLE [dbo].[Assocation](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CityID] [int] NULL,
 CONSTRAINT [PK_Assocation] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Center]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Center](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [AssociationID] [int] NULL,
 CONSTRAINT [PK_Center] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[City]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [RegionID] [int] NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Class]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [SchoolID] [int] NULL,
 CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Country]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[EntityLevel]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntityLevel](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [ParentID] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Group]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [EntityLevelID] [int] NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Region]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CountryID] [int] NULL,
 CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[School]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [CenterID] [int] NULL,
 CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[User]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Mobile] [varchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[UserJobs]    Script Date: 2017-04-04 3:47:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserJobs](
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [GroupID] [int] NOT NULL,
    [EntityID] [int] NOT NULL,
 CONSTRAINT [PK_UserJobs] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [UserID] ASC,
    [GroupID] ASC,
    [EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (1, N'KH', 1)
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (2, N'mkh_ass', 2)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (1, N'NorthCenter', 1)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (2, N'SouthCenter', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (1, N'Jeddah', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (2, N'MakkahCiry', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (1, N'Class1', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (2, N'Class2', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (3, N'class3', 2)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (4, N'class4', 2)
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (1, N'KSA')
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (2, N'UAE')
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (1, N'Country', NULL)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (2, N'Region', 1)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (3, N'City', 2)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (4, N'Association', 3)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (5, N'Center', 4)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (6, N'School', 5)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (7, N'Class', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (1, N'Country Manager', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (2, N'Region Manager', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (3, N'City Manager', 3)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (4, N'Association Manager', 4)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (5, N'Center Manager', 5)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (6, N'School Manager', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (7, N'Teacher', 7)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (1, N'Makkah', 1)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (2, N'Riyadh', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (1, N'School1', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (2, N'School2', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (3, N'School3', 2)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (1, N'UserA', N'000000')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (2, N'UserB', N'1111')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (3, N'UserC', N'2222')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (4, N'UserD', N'3333')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (5, N'UserE', N'4444')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (6, N'UserF', N'5555')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (7, N'UserG', N'6548')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (8, N'UserH', NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (9, N'UserI', NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (10, N'UserJ', NULL)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (1, 1, 1, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (2, 2, 2, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (3, 3, 3, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (4, 4, 4, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (5, 5, 5, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (6, 6, 6, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (7, 7, 7, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (8, 8, 2, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (9, 9, 3, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (10, 10, 4, 2)
GO

any solution for that ?

解决方案

IMHO you must build the whole Entities tree and then use it to JOIN with the other tables.

Having a look at your desired result it is not clear to me what is the relation between Entities and Cities, Regions, Class, etc.

Obviously according to the Name of the entity, I know that 1 = Country, 2 = Region and so on, but I can't find out any field on your table schema that allow to get this information other than:

CASE WHEN Entity.ID = 1 (SELECT Name FROM Country WHERE ID = Entity.ID) END
     WHEN Entity.ID = 2 (SELECT Name FROM Region WHERE ID = Entity.ID) END
     WHEN Entity.ID = 3 (SELECT Name FROM City WHERE ID = Entity.ID) END
     ...
END as EntityName

I'd suggest you to build a UDF or SP to get the name of the Entity and use it on the next script.

;WITH tree AS
(
    SELECT e1.ID, e1.Name, e1.ParentID, [level] = 1
    FROM   EntityLevel e1
    WHERE  e1.ParentID = (SELECT EntityID FROM UserJobs WHERE UserID = 1)
    UNION ALL
    SELECT     e2.ID, e2.Name, e2.ParentID, [level] = tree.[level] + 1
    FROM       EntityLevel e2 
    INNER JOIN tree 
    ON         e2.ParentID = tree.ID
)
SELECT     EntityLevelID, UserName, GroupID, GroupName, EntityID, EntityName
FROM       tree t
INNER JOIN (SELECT gr.entitylevelid, 
                   us.Name UserName, 
                   gr.Name GroupName, 
                   el.Name as EntityName,
                   gr.ID as GroupID,
                   el.ID as EntityID
            FROM   userjobs uj
            INNER JOIN [group] gr
            ON     gr.id = uj.groupid
            INNER JOIN entitylevel el
            ON     el.id = gr.entitylevelid
            INNER JOIN [user] us
            ON us.id = uj.userid) t1
ON t.ID = t1.EntityLevelID
OPTION (MAXRECURSION 0)
;

GO


EntityLevelID | UserName | GroupID | GroupName         | EntityID | EntityName 
------------: | :------- | ------: | :---------------- | -------: | :----------
            2 | User2    |       3 | region admin      |        2 | Region     
            2 | User3    |       4 | region Supervisor |        2 | Region     
            4 | User4    |       5 | manager           |        4 | Association
            5 | user5    |       6 | supervisor        |        5 | Center     
            7 | user6    |       7 | teacher           |        7 | Class

dbfiddle here

这篇关于SQL Server CTE层次结构问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆