Как правильно построить обобщенное иерархическое выражение?

Question

[Денис Кузнецов]

Здравствуйте, у меня есть таблица зп

и я пытаюсь построить запрос который выведем мне id и зп начиная с сотрудника с самой низкой зп и заканчивая с самой высокой
но он пишет, что входит в бесконечную рекурсию (я поставил границу на 1000)
вот сам запрос

WITH TestCTE(id, salary) 
AS 
( 
--Находим якорь рекурсии 
SELECT Employee_ID, 19000 as salary 
FROM Salary where Employee_ID = 4
UNION ALL 
--Делаем объединение с TestCTE (хотя мы его еще не дописали) 
SELECT s.Employee_ID, cte.salary + 1000 
FROM Salary s 
JOIN TestCTE cte ON s.Employee_ID = cte.id 
) 
SELECT * FROM TestCTE ORDER BY TestCTE.salary 
OPTION (MAXRECURSION 1000)

Comments

[ProTreo]

Не очень понятно, на основе чего вы собираетесь строить иерархию(отношение parent - child) и какой смысл в использовании recursive cte в данном случае, когда можно обойтись простым запросом с order by

[ProTreo]

DROP TABLE IF EXISTS #SalaryData
CREATE TABLE #SalaryData (
  Id INT NOT NULL PRIMARY KEY IDENTITY,
  EmployeeId INT NOT NULL,
  SalaryDate DATETIME CONSTRAINT DF__SalaryData__SalaryDate DEFAULT (GETUTCDATE()),
  Salary DECIMAL(19,4) NOT NULL CONSTRAINT DF__SalaryData__Salary DEFAULT (0.0)
)

INSERT #SalaryData(EmployeeId, Salary)
VALUES
(2, 30000.0),
(1, 40000.0),
(5, 40000.0),
(3, 30000.0),
(4, 19000.0)

-- Просто выборка по зарплате
SELECT *
FROM #SalaryData sd
ORDER BY sd.Salary DESC

-- Выборка зарплат и сотрудников, у которых зарплата больше чем у целевого сотрудника
;WITH CTE AS
(
  SELECT
    RootEmployeeId = sd.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = 0
  FROM #SalaryData sd

  UNION ALL

  SELECT
    RootEmployeeId = cte.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = cte.Depth + 1
  FROM CTE cte
    INNER JOIN #SalaryData sd ON sd.Salary > cte.Salary
)
SELECT
  cte.RootEmployeeId,
  STRING_AGG(CONCAT(cte.Salary, '(', cte.EmployeeId, ')'), ',') WITHIN GROUP (ORDER BY cte.Salary ASC)
FROM CTE cte
WHERE cte.Depth < 2
GROUP BY cte.RootEmployeeId

[Константин Цветков]

ProTreo, Перенесите в ответ, отмечу как решение.

STRING_AGG(CONCAT(cte.Salary, ' (', cte.EmployeeId, ')'), ', ')

эстетичнее.

Answer 1

[ProTreo]

DROP TABLE IF EXISTS #SalaryData
CREATE TABLE #SalaryData (
  Id INT NOT NULL PRIMARY KEY IDENTITY,
  EmployeeId INT NOT NULL,
  SalaryDate DATETIME CONSTRAINT DF__SalaryData__SalaryDate DEFAULT (GETUTCDATE()),
  Salary DECIMAL(19,4) NOT NULL CONSTRAINT DF__SalaryData__Salary DEFAULT (0.0)
)

INSERT #SalaryData(EmployeeId, Salary)
VALUES
(2, 30000.0),
(1, 40000.0),
(5, 40000.0),
(3, 30000.0),
(4, 19000.0)

-- Просто выборка по зарплате
SELECT *
FROM #SalaryData sd
ORDER BY sd.Salary DESC

-- Выборка зарплат и сотрудников, у которых зарплата больше чем у целевого сотрудника
;WITH CTE AS
(
  SELECT
    RootEmployeeId = sd.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = 0
  FROM #SalaryData sd

  UNION ALL

  SELECT
    RootEmployeeId = cte.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = cte.Depth + 1
  FROM CTE cte
    INNER JOIN #SalaryData sd ON sd.Salary > cte.Salary
)
SELECT
  cte.RootEmployeeId,
  STRING_AGG(CONCAT(cte.Salary, ' (', cte.EmployeeId, ')'), ', ') WITHIN GROUP (ORDER BY cte.Salary ASC)
FROM CTE cte
WHERE cte.Depth < 2
GROUP BY cte.RootEmployeeId