SQL Server CTE hierarchy issue

2020-03-02 13:44发布

站内文章 / SQL-Server
53 0 0

问题:

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 ?

回答1:

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



回答2:

Can you try this? (edited after comments)

;WITH MyCTE AS (
  SELECT T1.ID, UserId,  NULL AS PARENT_ID, T1.GroupID, G.EntityLevelID
  FROM UserJobs T1
  INNER JOIN [GROUP] G ON T1.GROUPID = G.ID
  inner join EntityLevel el on G.EntityLevelID = el.Id 
   WHERE T1.UserID = 1  /* Write here the user id you want */
  UNION ALL
  SELECT T2.ID, T2.UserId,  EL.ParentID, T2.GroupID, G.EntityLevelID
  FROM UserJobs T2
  INNER JOIN [GROUP] G ON T2.GROUPID = G.ID
  inner join EntityLevel el on G.EntityLevelID = el.Id
  INNER JOIN MyCTE itms ON EL.ParentID >= itms.ID 
)

SELECT B.*,  C.*, A.*
FROM (SELECT DISTINCT * FROM  MyCTE) A
INNER JOIN [USER] B ON A.UserID = B.ID
INNER JOIN [Group] C ON A.GroupID = C.ID
 ;

Output:

    ID  Name    Mobile  ID  Name    EntityLevelID   ID  UserId  PARENT_ID   GroupID EntityLevelID
1   1   Loai    000000  1   SA  1   1   1   NULL    1   1
2   3   User2   2222    3   region admin    2   3   3   1   3   2
3   4   User3   3333    4   region Supervisor   2   4   4   1   4   2
4   5   User4   4444    5   manager 4   5   5   3   5   4
5   6   user5   5555    6   supervisor  5   6   6   4   6   5
6   7   user6   6548    7   teacher 7   7   7   6   7   7


标签： sql-server common-table-expression hierarchical-data recursive-query
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~