Mysql - tablica ze struktura zatrudnienia

Question

Mam prośbę o pomoc w skonstruowaniu zapytania sql do tablicy zawierającej dwa pola.

pracownik i przełożony

W obu polach są przechowywane #id pracownika, poniżej przykład zawartości:

userid	przelozonyid
2	6
6	18
9	2
16	6

Chodzi o zapytanie, które wyświetli informację:

dla przełożonego ID=18

pracownik: 6 - > podwładni pracownika-> 16 i 2 -> podwładni: 9

dla przełożonego ID=6

pracownik: 16 i 2->podwładny ->9

 

Dziękuję za podpowiedź

Answer 1

select * from jobers;

+--------+--------+
| userid | overid |
+--------+--------+
|      2 |      6 |
|      6 |     18 |
|      9 |      2 |
|     16 |      6 |
+--------+--------+

#1 Try this:

select x2.userid as j1,x3.userid as j2,x4.userid as j3 from jobers as x2,jobers as x3,jobers as x4 where x2.overid=18 and x3.overid=x2.userid and x4.overid!=x2.overid and x4.overid!=x2.userid;

. Result:

+------+------+------+
| j1   | j2   | j3   |
+------+------+------+
|    6 |    2 |    9 |
|    6 |   16 |    9 |
+------+------+------+

#2:

select x2.userid as j1,x3.userid as j2 from jobers as x2,jobers as x3 where x2.overid=6 and x3.overid!=x2.overid and x3.overid!=(select jobers.overid from jobers where jobers.userid=x2.overid);

. Result:

+------+------+
| j1   | j2   |
+------+------+
|    2 |    9 |
|   16 |    9 |
+------+------+

Nie oto pewnie chodziło, więc podaję inne rozwiązanie:

select * from em;

+--------+--------+
| userid | overid |
+--------+--------+
|      7 |     18 |
|      6 |      7 |
|     14 |      7 |
|      8 |      7 |
|      3 |      6 |
|      2 |      3 |
|     13 |      8 |
|     10 |     13 |
|      1 |      8 |
|     12 |      1 |
|     14 |     12 |
|     15 |     14 |
|     16 |     14 |
|     17 |     14 |
+--------+--------+

set @x = 3;
  
SELECT distinct em1.userid as worker,em2.userid as sub_worker,em4.userid as subordinate from em
left join em as em1 on em1.overid = @x
left join em as em2 on (em2.overid in (em1.userid))
left join em as em4 on em4.overid not in (select userid from em where overid = @x) and em4.userid not in (SELECT userid from em where em.userid in (select userid from em where overid = @x));

. Zmieniając zmienną "x" ujrzysz najprawdopodobniej oczekiwane przez Ciebie rezultaty.

Dla: SET @x = 3;, wynik to:

+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
|      2 |       NULL |           7 |
|      2 |       NULL |           6 |
|      2 |       NULL |          14 |
|      2 |       NULL |           8 |
|      2 |       NULL |           3 |
|      2 |       NULL |          13 |
|      2 |       NULL |          10 |
|      2 |       NULL |           1 |
|      2 |       NULL |          12 |
|      2 |       NULL |          15 |
|      2 |       NULL |          16 |
|      2 |       NULL |          17 |
+--------+------------+-------------+

, a dla SET @x = 1;, taki:

+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
|     12 |         14 |           7 |
|     12 |         14 |           6 |
|     12 |         14 |          14 |
|     12 |         14 |           8 |
|     12 |         14 |           3 |
|     12 |         14 |           2 |
|     12 |         14 |          13 |
|     12 |         14 |          10 |
|     12 |         14 |           1 |
|     12 |         14 |          15 |
|     12 |         14 |          16 |
|     12 |         14 |          17 |
+--------+------------+-------------+

. Natomiast dla SET @x = 44;:

+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
|   NULL |       NULL |           7 |
|   NULL |       NULL |           6 |
|   NULL |       NULL |          14 |
|   NULL |       NULL |           8 |
|   NULL |       NULL |           3 |
|   NULL |       NULL |           2 |
|   NULL |       NULL |          13 |
|   NULL |       NULL |          10 |
|   NULL |       NULL |           1 |
|   NULL |       NULL |          12 |
|   NULL |       NULL |          15 |
|   NULL |       NULL |          16 |
|   NULL |       NULL |          17 |
+--------+------------+-------------+

. Powyższe dwa rozwiązania są błędne z założeń Twojego zadania o ile dobrze zrozumiałem, ostatecznym rozwiązaniem tego problemu jest ten kod:

SELECT distinct em1.userid as worker,em2.userid as sub_worker,em4.userid as subordinate from em
left join em as em1 on em1.overid = 1
left join em as em2 on em2.overid in (em1.userid)
left join em as em4 on em4.overid = (em2.userid);

.

Comments

Dziękuję bardzo, jednak dalej nie mogę sobie poradzić z tą tablicą i zapytaniem.

Testowałem to na tablicy:

userid	overid
7	18
6	7
14	7
8	7
3	6
2	3
13	8
10	13
1	8
12	1
14	12
15	14
16	14
17	14

 

I otrzymuję dziwne dane.

Będę bardzo zobowiązany za podpowiedź i pomoc. Dziękuję