Sql Query根据RoleID和Hierarchy Structure向下钻取到最低级别的表 [英] Sql Query to Drill down to a table to minimum level as per RoleID and Hierarchy Structure

问题描述

我们有一个应用程序，用户正在进入他们的时间。我们有两个表，生产表用于输入所有生产任务和NonProduction用于输入应用程序中的所有非生产相关任务。我们还有一个UserInfo表，其中存储了所有用户信息，如用户报告的eCode，Name，Designation，RoleID，ImmediateSupervisor ECode等。我们正在尝试以这种方式进行SQL查询，以便任何其roleid大于的用户1可以向下钻取并提取在他下报告的用户的生产和非生产信息，但条件是提取的报告仅为RoleID 1。例如，A Manager RoleID为4，因此首先他向下钻取并找到向他报告的所有用户（此信息位于UserInfo表的ImmediateSupervisor列中），即RoleID 3或RoleID 4，然后我们再次向下钻取这些用户并提取向我们报告并继续向下钻取到ROleID 1的信息，一旦我们深入到该级别，我们会提取相应的用户生产和非生产详细信息并显示在报告中。



以下是Create Table格式的表结构查询; -

生产： -

We have a application where users are entering their time efforts .We have two tables, Production table for entering all production tasks and NonProduction for entering all non production related tasks in the application.We also have a UserInfo table where all the user information is stored like his eCode, Name, Designation,RoleID,ImmediateSupervisor ECode to whom the user is reporting etc. We are trying to make a sql query in such manner so that any users whose roleid is greater then 1 can drill down and extract the Production and NonProduction information of the users reporting under him but the condition is that the extracted report is of RoleID 1 only. For example, A Manager RoleID is 4 ,so firstly he drill down and find all the users reporting to him (This info is in ImmediateSupervisor column on UserInfo table)i.e RoleID 3 or RoleID 4, then again we drill down to these Users and extract information who are reporting to them and continue until we drill down up to ROleID 1 and once we drill down to that level, we extract respective users Production and NonProduction details and display in the Report.

Below is the table structure query in Create Table format;-
Production:-

CREATE TABLE [dbo].[Production](
	[ProductionTimeEntryID] [int] IDENTITY(100,1) NOT NULL,
	[CalendarDate] [datetime] NOT NULL,
	[UserID] [int] NOT NULL,
	[NatureOfWorkID] [int] NOT NULL,
	[RegionProjectID] [int] NOT NULL,
	[CountyID] [int] NOT NULL,
	[WorkTypeID] [int] NOT NULL,
	[TaskID] [int] NOT NULL,
	[VolumeProcessed] [int] NOT NULL,
	[NosOfError] [int] NULL,
	[NosOfVolumeAudited] [int] NULL,
	[TimeSpent] [varchar](25) NULL,
	[Comment] [varchar](250) NULL,
	[IsTaskCompleted] [int] NOT NULL,
	[isCurrentDayTask] [bit] NOT NULL,
	[SupervisorECode] [nvarchar](50) NULL,
	CONSTRAINT [PK_Production] PRIMARY KEY CLUSTERED 
	(
	[ProductionTimeEntryID] 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
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_County] FOREIGN KEY([CountyID])
	REFERENCES [dbo].[County] ([CountyID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_County]
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_NatureOfWork] FOREIGN KEY([NatureOfWorkID])
	REFERENCES [dbo].[NatureOfWork] ([NatureOfWorkID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_NatureOfWork]
	GO
	ALTER TABLE [dbo].[Production]  WITH NOCHECK ADD  CONSTRAINT [FK_Production_RegionAndProjectInfo] FOREIGN KEY([RegionProjectID])
	REFERENCES [dbo].[RegionAndProjectInfo] ([RegionProjectID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_RegionAndProjectInfo]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_Task] FOREIGN KEY([TaskID])
	REFERENCES [dbo].[Task] ([TaskID])
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_Task]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_USERINFO] FOREIGN KEY([UserID])
	REFERENCES [dbo].[USERINFO] ([UserID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_USERINFO]
	GO
	ALTER TABLE [dbo].[Production]  WITH CHECK ADD  CONSTRAINT [FK_Production_WorkType] FOREIGN KEY([WorkTypeID])
	REFERENCES [dbo].[WorkType] ([WorkTypeID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	ALTER TABLE [dbo].[Production] CHECK CONSTRAINT [FK_Production_WorkType]
	GO
	ALTER TABLE [dbo].[Production] ADD  DEFAULT ((0)) FOR [IsTaskCompleted]
	GO
	ALTER TABLE [dbo].[Production] ADD  DEFAULT ((0)) FOR [isCurrentDayTask]
	GO

UserInfo表： -

UserInfo Table:-

CREATE TABLE [dbo].[USERINFO](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserECode] [nvarchar](50) NOT NULL,
	[UserName] [nvarchar](250) NOT NULL,
	[CCCode] [nvarchar](50) NULL,
	[CCName] [nvarchar](50) NULL,
	[Password] [varchar](50) NULL,
	[IsFlagEnabled] [bit] NULL,
	[IsFirstTimeUserLoggedIn] [bit] NULL,
	[EmailAddress] [nvarchar](250) NULL,
	[Designation] [varchar](50) NULL,
	[ShiftStartTime] [varchar](8) NULL,
	[ShiftEndTime] [varchar](8) NULL,
	[WeekendShiftStartTime] [varchar](8) NULL,
	[WeekendShiftEndTime] [varchar](8) NULL,
	[RoleID] [int] NULL,
	[ShiftEndFlagStatus] [int] NOT NULL,
	[ShiftStartTimeWithTimeStamp] [datetime] NULL,
	[ShiftEndTimeWithTimeStamp] [datetime] NULL,
	[LoggedInDateTime] [datetime] NULL,
	[FirstLoggedInOnthedayflag] [int] NOT NULL,
	[ImmediateSupervisor] [nvarchar](50) NULL,
	 CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED 
	(
		[UserID] 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
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [RoleID]
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [ShiftEndFlagStatus]
	GO
	ALTER TABLE [dbo].[USERINFO] ADD  DEFAULT ((1)) FOR [FirstLoggedInOnthedayflag]
	GO

请帮我进行此类查询，或者是否有人创建了此类型的查询，请分享，以便我也尝试在我的应用程序中加入相同的内容。感谢Advanse

Please help me to make such query or if any one had created such type of query, please share so that i also try to incorporate same in my application. Thanks in Advanse

推荐答案

with
	ProductionCTE(CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment)
	as
	(
	select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,
	R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment
	from production P inner join NatureOfWork N
	on N.NatureofWorkID=P.natureofworkid
	inner join dbo.RegionAndProjectInfo R
	on R.RegionProjectID=P.RegionProjectID
	inner join county C
	on C.countyid=P.countyid
	inner join worktype W
	on W.Worktypeid=P.worktypeID
	inner join task T
	on T.taskid=P.TaskID
	inner join UserInfo U
	on U.Userid=P.userid
	where P.userid=952
		
		union all
 
			select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,
			R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment
			from production P inner join NatureOfWork N
			on N.NatureofWorkID=P.natureofworkid
			inner join dbo.RegionAndProjectInfo R
			on R.RegionProjectID=P.RegionProjectID
			inner join county C
			on C.countyid=P.countyid
			inner join worktype W
			on W.Worktypeid=P.worktypeID
			inner join task T
			on T.taskid=P.TaskID
			inner join UserInfo U
			on U.Userid=P.userid
			inner join ProductionCTE
			on U.ImmediateSupervisor=ProductionCTE.UserECode
	)
    select distinct CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment from ProductionCTE order by UserECode

使用递归CTE，问题得到解决。

Using Recursive CTE , the issue is fixed.

这篇关于Sql Query根据RoleID和Hierarchy Structure向下钻取到最低级别的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！