gpt4 book ai didi

mysql - 如何将连接查询的两行输出合并为一行?

转载 作者:行者123 更新时间:2023-11-29 02:09:17 26 4
gpt4 key购买 nike

<分区>

我有两个表 Slot 和 Timetable
插槽:

slotID|startTime|endTime
------------------------
1 |10:00:00|12:00:00
2 |13:00:00|15:00:00

时间表:

ScheduleID|slotID|subjectID|day
-------------------------------
1 |1 |subject1 |mon
2 |2 |subject1 |mon
3 |1 |subject2 |tue
4 |2 |subject2 |tue

我试过了

    SELECT slot.startTime, slot.endTime,
(CASE WHEN day="mon" THEN timetable.subjectID END) as Monday,
(CASE WHEN day="tue" THEN timetable.subjectID END) as Tuesday
FROM timetable
INNER JOIN slot ON timetable.slotID=slot.slotID

我得到了:

startTime|endTime|Monday  |Tuesday
---------------------------------------
10:00:00|12:00:00|subject1|NULL
10:00:00|12:00:00|NULL |subject2
13:00:00|15:00:00|subject1|NULL
13:00:00|15:00:00|NULL |subject2

我想编写一个 SQL 查询来输出 8 列来打印如下所示的内容

startTime |endTime |   mon  |   tue  |   wed  |   thu  |   fri  |   sat  
-------------------------------------------------------------------------
10:00:00 |12:00:00|subject1|subject2|subject1|subject2|subject1|subject2
13:00:00 |15:00:00|subject1|subject2|subject1|subject2|subject1|subject2

我想删除 NULL 值并合并具有相同开始时间和结束时间的行。任何帮助将不胜感激

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com