时间戳

首页 > 解决方案 > 在 SQL Server 2014 中呈现具有不同条件的分层数据

sql-server - 在 SQL Server 2014 中呈现具有不同条件的分层数据

问题描述

我有两张桌子。在一张表中我的原始数据和在另一张表中的关系如下

Declare @Emp table(EmpId int,EmpName Varchar(100),CITY VARCHAR(100),Designation Varchar(100),ReportingManager Int)
INSERT INTO @Emp
VALUES(1,'Ram','Hyderabad','TL',6)
,(2,'Laxman','Hyderabad','TL',9)
,(3,'Suresh','Bangalore','Officer',6)
,(4,'Rajesh','Bangalore','Officer',9)
,(5,'Lokesh','Delhi','TL',6)
,(6,'Venkatesh','Mumbai','Manager',6)
,(7,'Subbu','Patna','Officer',9)
,(8,'Ravi','Hyderabad','Officer',9)
,(9,'Sai','Hyderabad','Manager',9)
,(10,'Satish','Hyderabad','Officer',6)

DECLARE @EmpRelation TABLE(EmpRelationShipID INT IDENTITY NOT NULL,ReportingTo INT,EmpID INT)
INSERT INTO @EmpRelation
VALUES(1,6)
,(2,9)
,(3,1)
,(4,5)
,(5,6)
,(7,2)
,(8,5)
,(10,1)

我想要如下数据

[EMPID],[EMPNAME],[CITY],[IsManager],[HasSubordinates],[IsSubordinate],[ManagerCity],[SubordinatesList]

我无法获取正确的数据。任何人都可以看看这个并建议我查询这个。

标签： sql-servertsql

解决方案

下面的例子应该给你一些想法。它不会遍历层次结构，即使用递归 CTE，因为似乎没有结果依赖于检查层次结构的上层或下层。

select E.EmpId, E.EmpName, E.City, E.Designation,
  -- Anyone who reports to themselves is a manager.
  case when E.EmpId = E.ReportingManager then 'Yes' else 'No' end as IsManager,
  -- Anyone with a related employee reporting to them has subordinates.
  case when exists ( select 42 from @EmpRelation as iER where iER.ReportingTo = E.EmpId )
    then 'Yes' else 'No' end as HasSubordinates,
  -- Anyone who reports to an employee is a subordinate.  (Should this exclude reporting to themselves?)
  case when exists ( select 42 from @EmpRelation as iER where iER.EmpId = E.EmpId )
    then 'Yes' else 'No' end as IsSubordinate,
  -- The city of the reporting manager, if any.
  ME.City as ManagerCity,
  -- A comma-delimited list of employees who report directly to the current employee.
  --   Modern versions of SQL Server could use   String_Agg .
  Stuff( (
    select ',' + sE.EmpName
      from @Emp as sE inner join @EmpRelation as sER on sER.EmpId = se.EmpId
      where sER.ReportingTo = E.EmpId
      order by sE.EmpName for XML path(''), type).value('.[1]', 'VarChar(max)' ),
    1, 1, '' ) as SubordinatesList
  from @Emp as E left outer join
    @Emp as ME on ME.EmpId = E.ReportingManager;

感谢您提供可用的样本数据。

根据 OP 的评论，@EmpRelation这张桌子有点令人困惑。@EmpRelation有(3,1)和(10,1)作为(ReportingTo,EmpId)。这并不意味着EmpId 1is ReportingTo 3and 10（也称为矩阵管理）。修改查询以翻转关系需要对和进行细微更改HasSubordinates：IsSubordinateSubordinateList

select E.EmpId, E.EmpName, E.City, E.Designation,
  -- Anyone who reports to themselves is a manager.
  case when E.EmpId = E.ReportingManager then 'Yes' else 'No' end as IsManager,
  -- Anyone with a related employee reporting to them has subordinates.
  case when exists ( select 42 from @EmpRelation as iER where iER.EmpId = E.EmpId )
    then 'Yes' else 'No' end as HasSubordinates,
  -- Anyone who reports to an employee is a subordinate.  (Should this exclude reporting to themselves?)
  case when exists ( select 42 from @EmpRelation as iER where iER.ReportingTo = E.EmpId )
    then 'Yes' else 'No' end as IsSubordinate,
  -- The city of the reporting manager, if any.
  ME.City as ManagerCity,
  -- A comma-delimited list of employees who report directly to the current employee.
  --   Modern versions of SQL Server could use   String_Agg .
  Stuff( (
    select ',' + sE.EmpName
      from @Emp as sE inner join @EmpRelation as sER on sER.ReportingTo = se.EmpId
      where sER.EmpId = E.EmpId
      order by sE.EmpName for XML path(''), type).value('.[1]', 'VarChar(max)' ),
    1, 1, '' ) as SubordinatesList
  from @Emp as E left outer join
    @Emp as ME on ME.EmpId = E.ReportingManager;

推荐阅读