Блог

Мне нужно сгенерировать список кодов отделов пользователей. Если у пользователя нет кода, получите его код диспетчера и так далее по иерархии.

Исходная таблица выглядит следующим образом:

 manager emp code ----------------------- boss subboss AAA boss subsub SUBCODE subboss john ABC subboss alan (null) (null) boss ZZZ subsub steve (null) steve rick (null)  CREATE TABLE Users (  [manager] varchar(10),   [emp] varchar(10),   [code] varchar(10) );   INSERT INTO Users ([manager], [emp], [code]) VALUES  ('boss', 'subboss', 'AQQ'),  ('boss', 'subsub', 'SUBSUB'),  ('subboss', 'john', 'ABC'),  ('subboss', 'alan', null),  (null, 'boss', 'ZZZ'),  ('subsub', 'steve', null),  ('steve', 'rick', null);  

Желаемый результат таков:

 manager emp code ------------------------ boss subboss AAA boss subsub SUBCODE subboss john ABC subboss alan AAA (null) boss ZZZ subsub steve SUBCODE steve rick SUBCODE  

Моя первая попытка-это:

 select   manager, emp,  coalesce(code, (select code from Users u1 where u.manager = u1.code)) from   Users u;  

Но он возвращает только код прямого менеджера.

Я был бы признателен за советы о том, как сделать это рекурсивно с CTE.

Попробуйте что-нибудь вроде этого:

 WITH Hierarchy AS (  -- create the "anchor" - the toplevel node(s)  SELECT  u.emp, u.manager, u.code, 0 AS Level  FROM  Users u  WHERE  u.manager IS NULL   UNION ALL   -- recursive part - join subordinate to manager, one level up  SELECT  u.emp, u.manager, COALESCE(u.code, h.code), h.Level   1  FROM  Hierarchy h  INNER JOIN  Users u ON u.manager = h.emp ) SELECT  * FROM  Hierarchy  

В результате получается набор данных, подобный этому:

 emp manager code Level ----------------------------- boss NULL ZZZ 0 subboss boss AQQ 1 subsub boss SUBSUB 1 steve subsub NULL 2 rick steve NULL 3 john subboss ABC 2 alan subboss NULL 2