sql - SQLServer 通过两个条件将结果限制为 1

问题描述

我的公司提供不同的课程。一门课程通常持续五天，通常由一位老师教授。但是，也有一些例外情况，即一门课程在两位老师之间分开。目前我有一个 SQL 语句，它在课程的第一天选择（或“限制”）结果给老师：

SELECT c.course_id,
       c.description,
       t.firstname AS teacher_firstname,
       t.lastname AS teacher_lastname,
       cd.day AS first_day,
       cd2.day AS last_day
FROM (SELECT *,
             ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY day) AS rn
      FROM Course_days) cd
     JOIN (SELECT *,
                  ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY day DESC) AS rn2
           FROM Course_days) cd2 ON cd.course_id = cd2.course_id
     JOIN Courses c ON cd.course_id = c.course_id
     JOIN Teachers t ON t.teacher_id = cd.teacher_id
WHERE rn = 1
  AND rn2 = 1;

它返回如下内容：

course_id	描述	老师名字	老师姓氏	第一天	最后一天
101	HTML/CSS/JS 基础知识 2021 年 2 月	约翰	能源部	2021-02-01	2021-02-05
102	C# 2021 年 2 月	简	能源部	2021-02-01	2021-02-05
...	...	...	...	...	...

正如您在下面的 DB 结构中看到的，课程 101 有两名教师（粗体条目）。

有没有可能得到这样的结果？

course_id	描述	老师名字	老师姓氏	second_teacher_firstname	second_teacher_lastname	第一天	最后一天
101	HTML/CSS/JS 基础知识 2021 年 2 月	约翰	能源部	先生。	X	2021-02-01	2021-02-05
102	C# 2021 年 2 月	简	能源部	无效的	无效的	2021-02-01	2021-02-05
...	...	...	...	...	...

我已经花了几个小时寻找一种可能的方法来做到这一点，但并没有真正找到任何东西。

数据库结构：

培训班：

course_id	描述
...	...
101	HTML/CSS/JS 基础知识 2021 年 2 月
102	C# 基础知识 2021 年 2 月
103	Java 基础知识 2021 年 2 月
104	HTML/CSS/JS 基础知识 2021 年 3 月
105	C# 基础知识 2021 年 3 月
106	Java 基础知识 2021 年 3 月
...	...

教师：

教师编号	名	姓
1	约翰	能源部
2	简	能源部
3	前任	充足
4	先生。	X
...	...	...

Course_Days:

course_day_id	course_id （外键）	Teacher_id （外键）	天	course_day_number
...	...	...	...	...
548	101	1	2021-02-01	1
549	101	1	2021-02-02	2
550	101	1	2021-02-03	3
551	101	1	2021-02-04	4
552	101	4	2021-02-05	5
553	102	2	2021-02-01	1
554	102	2	2021-02-02	2
555	102	2	2021-02-03	3
556	102	2	2021-02-04	4
557	102	2	2021-02-05	5
...	...	...	...	...

标签： sqlsql-server

那些老师很棘手，因为您（大概）想要保留原始顺序。

这里采用的方法可以推广到更多的教师，并保持名称有序。对于每位教师，它决定了教师教授课程的第一天。然后它使用条件聚合来定义“teacher1”和“teacher2”：

SELECT c.course_id, c.description,
       MAX(CASE WHEN teacher_seqnum = 1 THEN t.firstname END) AS teacher1_firstname,
       MAX(CASE WHEN teacher_seqnum = 1 THEN t.lastname END) AS teacher1_lastname,
       MAX(CASE WHEN teacher_seqnum = 2 THEN t.firstname END) AS teacher2_firstname,
       MAX(CASE WHEN teacher_seqnum = 2 THEN t.lastname END) AS teacher2_lastname,
       MIN(day) as first_day,
       MAX(day) as last_day
FROM (SELECT cd.*,
             dense_rank() OVER (PARTITION BY course_id, teacher_id ORDER BY min_teacher_day) as teacher_seqnum
       FROM (SELECT c.description, cd.*,
                    t.firstname, t.lastname,
                    ROW_NUMBER() OVER (PARTITION BY cd.course_id ORDER BY cd.day) AS seqnum,
                    MIN(cd.day) OVER (PARTITION BY cd.course_id, cd.teacher_id) as min_teacher_day
             FROM Course_days cd JOIN
                  Teachers t
                  ON t.teacher_id = cd.teacher_id JOIN
                  Courses c
                  ON c.course_id = cd.course_id
            ) cd
     ) cd
GROUP BY course_id, description

sql - SQLServer 通过两个条件将结果限制为 1

问题描述

解决方案

推荐阅读