Question

怎样修改多条sql子查询语句用union把结果集连接起来。

图片一是组的层次管理，根据该层次权限设置，如果我是江夏呼叫中心登陆，我能看到江夏呼叫中心(同组)和同组下商务一部、二部等和广告组推广组等其他人员信息。图二我用sql语句已经实现了。但是当我广告组下面继续分组，那么我sql语句后面就要继续union了，请问该怎样解决这种问题，怎样写出正确的sql。
higher字段是lower父节点。
sqlserver2008数据库。





Answer 1

如果是想取出江厦呼叫中心的父和所有层的子，那就如下

with t1 as (
  select * from authority_relationship where id='江厦呼叫中心'
  union all
  select s.* from authority_relationship s join t1 on s.lower=t1.id
),
t2 as (
  select * from authority_relationship where id='江厦呼叫中心'
  union all
  select s.* from authority_relationship s join t2 on s.higher=t2.id
)
select * from t1
union
select * from t2

Answer 2

你这部分需要recursive query。
下面是 sample code， 具体逻辑需要你自己添进去，这个可以搜索无限深的层次。
http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/

USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO