学生每周（课程）时间表 [英] Weekly (Course) Schedule for Students

问题描述

I have a table that includes student's registered courses plus day, start and end hours. 

基本查询是这样的：

SELECT SELECT courseName，courseDay，courseStartHour，coursEndHour
FROM courses WHERE studentId = 1

我想向学生展示每周的时间表：



这对我来说很难，而且有一个更难的部分。两门课程可能有冲突。

This is hard for me already and there is a harder part. Two courses can conflict.

所以星期一10-11可以包括CourseName3。这些只能用SQL吗？

So Monday 10-11 can include CourseName3. Are these possible with just SQL?

示例数据：

Sample Data:

CREATE TABLE StudentCourses ( courseCode varchar2(8) NOT NULL,  courseName varchar2(64) NOT NULL,  day number(10),  startHour number(10),  endHour number(10));

INSERT INTO StudentCourses（courseCode， courseName，day，startHour，endHour）VALUES（'ADV 352'，'广告文案'，'1'，'9'，'11'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day ，startHour，endHour）VALUES（'ADV 352'，'广告文案'，'1'，'11'，'13'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour， endHour）VALUES（'ADV 332'，'广告中的定位策略'，'2'，'9'，'12'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour） ）VALUES（'COMM 324'，'劝说和Per ception'，'2'，'14'，'17'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES（'ADV 312'，'Corporate Communications Practicum' ，'3'，'14'，'17'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES（'ADV 302'，'Strategic Media Planning'，' 4'，'9'，'11'）; $
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES（'ADV 302'，'Strategic Media Planning'，'4' ，'11'，'13'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES（'ADV 412'，'广告中的案例研究'，'4'， '13'，'15'）; $
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES（'ADV 411'，'广告摄影'，'4'，'14' ，'16'）;
$
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUE S（'ADV 412'，'广告中的案例研究'，'4'，'15'，'17'）; b
INSERT INTO StudentCourses（courseCode，courseName，day，startHour，endHour）VALUES （'ADV 411'，'广告摄影'，'4'，'16'，'18'）;

$

INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 332','Positioning Strategy in Advertising','2','9','12' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'COMM 324','Persuasion and Perception','2','14', '17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 312','Corporate Communications Practicum','3','14','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','13','15' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','14','16' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','15','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','16','18' );

推荐答案

美好的一天Lac，

Good day Lac,

乍一看听起来像是一个PIVOT查询：

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

It sound like a PIVOT query in first glance:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

请提供查询以创建相关表格和
插入样本数据 以便我们能够快速提供解决方案

Please provide queries to create the relevant table and insert the sample data, so we will be able to provide solution fast

这篇关于学生每周（课程）时间表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！