我希望您能找到以下答案，直到提出更理想的解决方案为止.

第一步是创建一个新视图，其中记录了员工的级别.在这种情况下，名为"G"的老板"具有最小的值1.而对于每个级别的员工，其等级正在增加，其中A的***别为4.该视图使用层次查询构造为http://sqlfiddle.com/#!9/cd4be8

This sample data results in this hierarchy.

Each employee has a direct boss. Also, all the employees who are senior to a given employee are referred to as their indirect bosses. In the given sample, the direct boss of A is B, and C, D, E, F, G, and H are A's indirect bosses. I am required to find two things.

1. For each employee, find the count of all the employees who are senior to that employee (number of direct boss + indirect bosses).

Expected output:

+---------+-------+
| empName | total |
+---------+-------+
| A       |     7 |
| B       |     3 |
| C       |     3 |
| D       |     1 |
| E       |     3 |
| F       |     1 |
| G       |     0 |
| H       |     3 |
+---------+-------+

2. For each employee, find the indirect boss in such a way that indirect boss's salary is at least the twice the salary of the given employee but minimum among the indirect bosses.

Expected output:

+---------+------+
| empName | mgr  |
+---------+------+
| A       | D    |
| B       | G    |
| C       | NULL |
| D       | NULL |
| E       | NULL |
| F       | NULL |
| G       | NULL |
| H       | NULL |
+---------+------+

Explanation: for employee A (salary = 100), the indirect bosses with at least double salary are D (salary = 200), F (salary = 210) AND G (salary = 300). But since D's salary is minimum for D, F and G, the result is D.

It is easy to find the count of an employee's subordinates, but the other way round is something very tricky. Any help/hints would be highly appreciated.

I hope you will find the following answers helpful until a more optimal solution comes up.

The first step creates a new view where the level of the employee is recorded. The "boss", named G in this occasion, has the smallest value 1. While the rank is increasing for each level of employee with the highest been 4 for A. The view is constructed with a hierarchical query as here.

After creating the view, the first question is answered by summing all the employees in the higher rank. This is done for every rank and the join brings the information to the final view.

The second question is answered on a more brute force way. A self-join to produce all the viable combinations of employee and bosses is performed. After that the lines with the minimum boss salary are produced.

--Creating a view with the level of the diagram
CREATE VIEW MyCTEView 
AS 
    WITH my_anchor AS (
        SELECT boss.[empId], boss.[empName], boss.[mgrId], 1 AS EmpLevel
        FROM [MySchema].[dbo].[employee] AS boss
        WHERE boss.[mgrId]=boss.[empId]

        UNION ALL

        SELECT Emp.[empId], Emp.[empName], Emp.[mgrId], EL.EmpLevel+1
        FROM [MySchema].[dbo].[employee] AS Emp
        INNER JOIN my_anchor as EL
        ON Emp.[mgrId] = EL.[empId]
        WHERE Emp.[mgrId]<>Emp.[empId]
        )
    SELECT * FROM my_anchor;


--Answer to the first question
SELECT A.[empName]
      ,temp.direct_and_indirect-1 AS your_total
FROM [MySchema].[dbo].[MyCTEView] AS A
LEFT JOIN (
SELECT [EmpLevel],SUM(direct_subortinates) OVER(ORDER BY [EmpLevel]) AS direct_and_indirect
FROM (SELECT COUNT([mgrId]) AS direct_subortinates,[EmpLevel]
            FROM [MySchema].[dbo].[MyCTEView] GROUP BY [EmpLevel])T) AS Temp
ON Temp.[EmpLevel]=A.[EmpLevel]
ORDER BY A.[empName]


--Answer to the second question. Creating a CTE with all the viable combinations of employee and manager based on criteria.
--Displaying the information for the minimum
WITH cte AS (
SELECT A.[empId] as emId
      ,A.[empName] as emName
      ,A.[salary] as emsalary
      ,A.[EmpLevel] as emLevel
      ,B.[empId] as bossId
      ,B.[empName] as bossName
      ,B.[salary] as bosssalary
      ,B.[EmpLevel] as bossLevel
  FROM [MySchema].[dbo].[MyCTEView] AS A
  INNER JOIN 
  [MySchema].[dbo].[MyCTEView] AS B
  ON A.empId<>B.empId AND A.[EmpLevel]>B.[EmpLevel] AND B.[salary]>=2*A.[salary]
  )
SELECT tb1.emName, tb1.bossName 
FROM cte AS tb1
  INNER JOIN
  (
    SELECT emName, MIN(bosssalary) MinSalary
    FROM cte
    GROUP BY emName
  )tb2
  ON  tb1.emName=tb2.emName
  WHERE tb1.bosssalary=tb2.MinSalary