Discussion :

[agemis31]

Bonjour à tous,

En attendant les résultats du challenge 7, le 8ème challenge de la série des TSQL Challenges est disponible. Cette fois-ci il s'agit d'un problème d'arborescence.

Ces petits challenges sont destinés à s'amuser avec T-SQL tout en apprenant de nouvelles choses.

Attention, la date limite de réponse est le 1er juin 2009 !

Le challenge traduit en français est disponible ici.

Bon amusement

[mikedavem]

Bonsoir,

Bon aller j'avais un petit moment alors je me lance

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)   
INSERT INTO @Employees(EmpID, EmpName, ReportsTo)       
SELECT 1, 'Jacob', NULL UNION ALL       
SELECT 2, 'Rui', NULL UNION ALL       
SELECT 3, 'Jacobson', NULL UNION ALL       
SELECT 4, 'Jess', 1 UNION ALL       
SELECT 5, 'Steve', 1 UNION ALL       
SELECT 6, 'Bob', 1 UNION ALL       
SELECT 7, 'Smith', 2 UNION ALL       
SELECT 8, 'Bobbey', 2 UNION ALL       
SELECT 9, 'Steffi', 3 UNION ALL       
SELECT 10, 'Bracha', 3 UNION ALL       
SELECT 11, 'John', 5 UNION ALL       
SELECT 12, 'Michael', 6 UNION ALL       
SELECT 13, 'Paul', 6 UNION ALL       
SELECT 14, 'Lana', 7 UNION ALL       
SELECT 15, 'Johnson', 7 UNION ALL       
SELECT 16, 'Mic', 8 UNION ALL       
SELECT 17, 'Stev', 8 UNION ALL       
SELECT 18, 'Paulson', 9 UNION ALL       
SELECT 19, 'Jessica', 10   
 
DECLARE @manager VARCHAR(20)   
SELECT @manager = 'Jacob';
 
WITH cte_employees (EmpID,EmpNameBrut,EmpName,level,path_,sort)
AS
(
	SELECT
		EmpID,
		EmpName,
		CAST(EmpName AS VARCHAR(200)),
		0,
		CAST('|' AS VARCHAR(MAX)),
		CAST(EmpID AS VARCHAR(200))
	FROM @Employees emp
	WHERE ReportsTo IS NULL
	UNION ALL
	SELECT 
		emp.EmpID,
		emp.EmpName,
		CAST(SPACE(4 * (cte.level + 1)) + emp.EmpName AS VARCHAR(200)),
		cte.level + 1,
		cte.path_ + CAST(emp.ReportsTo AS VARCHAR(MAX)) + '|',
		CAST(sort + '|' + CAST(emp.EmpID AS VARCHAR(2)) AS VARCHAR(200))
	FROM @Employees emp
	INNER JOIN cte_employees cte
	 ON emp.ReportsTo = cte.EmpID
)
SELECT 
	emp1.EmpName
FROM cte_employees emp1
WHERE CHARINDEX('|' + (SELECT CAST(emp3.EmpID AS VARCHAR(2))
               FROM cte_employees emp3
               WHERE emp3.EmpNameBrut = @Manager) + '|',path_) <> 0
 OR emp1.EmpID = (SELECT emp2.EmpID 
		           FROM cte_employees emp2
                   WHERE emp2.EmpNameBrut = @Manager)
ORDER BY sort;

++

[agemis31]

Merci Mikedavem pour cette implémentation de H|i|e|r|a|r|c|h|y|I|D

[SQLpro]

Aller, plus simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
DECLARE @manager VARCHAR(20);
SET @manager = 'Smith';

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH 
T0 AS
(SELECT EmpID, EmpName, ReportsTo, 
        SPACE(10 - LEN(CAST(EmpID AS VARCHAR(max)))) + CAST(EmpID AS VARCHAR(max)) AS Pth, --> this give the path to order
        0 AS Lvl --> this give the level to space names
 FROM   @Employees
 WHERE  EmpName = @manager 
    OR  (@manager IS NULL AND ReportsTo IS NULL)
 UNION  ALL
 SELECT SUB.EmpID, SUB.EmpName, SUB.ReportsTo, Pth + SPACE(10 - LEN(CAST(SUB.EmpID AS VARCHAR(max)))) + CAST(SUB.EmpID AS VARCHAR(max)), Lvl + 1
 FROM   T0
        INNER JOIN @Employees AS SUB
              ON SUB.ReportsTo = T0.EmpID)
SELECT SPACE(Lvl * 3) + EmpName
FROM   T0
ORDER  BY Pth

A +

[agemis31]

Merci SQLPro,

C'est plus simple en effet. Mais une condition à été ajoutée au challenge pour le complexifier un peu : L'utilisation d'une cte est obligatoire, mais on ne peut pas filtrer sur @manager dans la cte

Désolé pour le désagrément.

[mikedavem]

Merci SQLPro,

C'est plus simple en effet. Mais une condition à été ajoutée au challenge pour le complexifier un peu : L'utilisation d'une cte est obligatoire, mais on ne peut pas filtrer sur @manager dans la cte

Désolé pour le désagrément.

Oui c'était trop simple sinon

[SQLpro]

Ce n'est pas plus difficile, mais c'est plus couteux !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH 
T0 AS
(SELECT EmpID, EmpName, ReportsTo, 
        SPACE(10 - LEN(CAST(EmpID AS VARCHAR(max)))) + CAST(EmpID AS VARCHAR(max))+'-' AS Pth, --> this give the path to order
        0 AS Lvl --> this give the level to space names
 FROM   @Employees
 UNION  ALL
 SELECT SUB.EmpID, SUB.EmpName, SUB.ReportsTo, Pth + SPACE(10 - LEN(CAST(SUB.EmpID AS VARCHAR(max)))) + CAST(SUB.EmpID AS VARCHAR(max)) +'-', Lvl + 1
 FROM   T0
        INNER JOIN @Employees AS SUB
              ON SUB.ReportsTo = T0.EmpID)
SELECT SPACE(Lvl * 4) + EmpName
FROM   T0
WHERE  LTRIM(Pth) LIKE CAST((SELECT EmpID 
                             FROM   @Employees 
                             WHERE  EmpName = @manager) AS VARCHAR(32)) + '-%'
ORDER  BY Pth

A +

[Waldar]

J'ai envoyé cette solution-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
DECLARE @manager VARCHAR(20)
SELECT @manager = 'Smith';
 
WITH CTEmp(EmpID, EmpName, Lvl, InitName, Ord) AS 
(
select
    Emp.EmpID,
    Emp.EmpName,
    0,
    Emp.EmpName,
    cast(0 as float(53))
from
    @Employees as Emp
/* The next filter is to not show employee who have no one reporting to them
where
    exists (select null from @Employees as Emp2
            where Emp2.ReportsTo = Emp.EmpID)
*/
union all
select
    E.EmpID,
    E.EmpName,
    C.Lvl + 1,
    C.InitName,
    cast(C.Ord + (E.EmpID * POWER(100.0000, -1*(C.Lvl +1))) as float(53))
from
    @Employees as E
    inner join CTEmp as C
      on E.ReportsTo = C.EmpID
)
  select SPACE(Lvl*4) + EmpName as [Managed by]
    from CTEmp
   where InitName = @manager
order by Ord asc;

[agemis31]

Very nice indeed Waldar ! May the POWER() of SPACE() be with you

@+