Saya memiliki tabel berikut:

rooms

rid | roomname   | ...
----------------------
1   | Pantry     | ...
2   | Conference | ...
...

employees

eid | lastname  | firstname
---------------------------
1   | Zufall    | Rainer
2   | Piper     | Peter
.... 

assignments

aid | room | employee | weekday
-------------------------------
1   |  2   |   1      |   1
2   |  2   |   1      |   2
3   |  2   |   2      |   3
4   |  2   |   1      |   4
5   |  2   |   1      |   5
6   |  1   |   2      |   1
7   |  1   |   2      |   3
8   |  1   |   2      |   5 

Sekarang saya ingin menulis kueri, mencantumkan saya semua kamar dan tugasnya dalam format berikut:

roomname   | Monday | Tuesday | Wednesday | Thursday | Friday
--------------------------------------------------------------
Conference | Zufall | Zufall  |   Piper   |  Zufall  |  Zufall
Pantry     | Piper  |  NULL   |   Piper   |   NULL   |  Piper

Saya menulis pertanyaan

SELECT r.roomname, e.lastname AS Monday
FROM rooms AS r
LEFT JOIN assignments AS a ON r.rid = a.room AND a.weekday = 1
LEFT JOIN employees AS e ON a.employee = e.eid

Apa yang memberi saya semua tugas untuk hari Senin, tetapi bagaimana saya bisa menggabungkannya dengan tugas di hari kerja lainnya?

Terima kasih sebelumnya. Lars

0
Gardinero 27 Januari 2020, 15:52

2 jawaban

Jawaban Terbaik

Anda dapat melakukan agregasi bersyarat:

select 
    r.roomname, 
    max(case when a.weekday = 1 then e.lastname end) monday,
    max(case when a.weekday = 2 then e.lastname end) tuesday,
    max(case when a.weekday = 3 then e.lastname end) wednesday,
    max(case when a.weekday = 4 then e.lastname end) thursday,
    max(case when a.weekday = 5 then e.lastname end) friday
from rooms as r
left join assignments as a on r.rid = a.room
left join employees as e on a.employee = e.eid
group by r.roomname, r.id
1
GMB 27 Januari 2020, 12:54

Gunakan agregasi bersyarat:

SELECT r.roomname,
       MAX(CASE WHEN a.weekday = 1 THEN e.lastname END) AS Monday,
       MAX(CASE WHEN a.weekday = 2 THEN e.lastname END) AS Tuesday,
       . . .
FROM rooms r LEFT JOIN
     assignments a
     ON r.rid = a.room LEFT JOIN
     employees e
     ON a.employee = e.eid
GROUP BY r.roomname;
1
Gordon Linoff 27 Januari 2020, 12:54