sql - SQL Query School Course pre 必修课

Question

脚本：提供构建模式脚本。对于每个学生，如果他错过了任何先决条件，则需要打印，加上申请表中的当前申请课程。它不应打印已在注册表中注册的任何课程。. 脚本内给出的预期结果。

create table applications ( student_id INT, requested_course VARCHAR(4) ) ;
INSERT INTO applications ( student_id, requested_course )
values ( 1,'ML'),(2,'CMP'),(3,'ML');

CREATE TABLE register
(student_id int, registered varchar(4))
;

INSERT INTO register
(student_id, registered)
VALUES
(1, 'HS'),
(1, 'PHY'),
(1, 'CMP'),
(2, 'MTH')
;

CREATE TABLE prerequisites
(course VARCHAR(4),prerequisite_course VARCHAR(4))
;

INSERT INTO prerequisites
(course, prerequisite_course)
VALUES
('MTH', 'HS'),
('PHY', 'HS'),
('CHM', 'HS'),
('CMP', 'MTH'),
('ML', 'CMP'),
('AP', 'CMP')
;

最终查询（我的尝试） - 不工作。

Select rq.student_id, rq.requested_course as course from applications rq
Where not exists ( select 1 from register rg where
rg.student_id=rq.student_id and rg.registered=rq.requested_course)

Union

Select rq.student_id,st.prerequisite_course as course from applications rq
Join prerequisites st ON st.course=rq.requested_course
Where not exists ( select 1 from register rg where
rg.student_id=rq.student_id and rg.registered=rq.requested_course
and st.prerequisite_course=rg.registered )
order by student_id
;

预期结果

Student_id Course
-----------------
  1.    'MTH'
  1     'ML'
  2     'HS'
  2     'CMP'
  3     'HS'
  3     'MTH'
  3     'CMP'
  3     'ML'

对于学生 ID 1：他正在选择 ML。因此它将被包括在内。他已经注册了 CMP。所以 CMP 被排除在外。现在他没有做 pre-req MTH 。所以最终结果：MTH 和 ML

对于学生 ID 2：他选择了 CMP。pre-req MTH 之一在寄存器表中，HS 不在。所以预期的结果：CMP，HS。

对于学生 ID 3：选择 ML。所以 ML 包括在内。注册表中没有先决条件。因此对于 3，预期结果为 HS、MTH、CMP、ML。

标签： sql

Answer 1

创建和插入表格脚本

create table #applications ( student_id INT, requested_course VARCHAR(4) ) ;
INSERT INTO #applications ( student_id, requested_course )
values ( 1,'ML'),(2,'CMP'),(3,'ML');

CREATE TABLE #register
(student_id int, registered varchar(4))
;

INSERT INTO #register
(student_id, registered)
VALUES
(1, 'HS'),
(1, 'PHY'),
(1, 'CMP'),
(2, 'MTH')
;

CREATE TABLE #prerequisites
(course VARCHAR(4),prerequisite_course VARCHAR(4))
;

INSERT INTO #prerequisites
(course, prerequisite_course)
VALUES
('MTH', 'HS'),
('PHY', 'HS'),
('CHM', 'HS'),
('CMP', 'MTH'),
('ML', 'CMP'),
('AP', 'CMP')
;

使用 while 循环和递归 CTE 的逻辑

If(OBJECT_ID('tempdb..#temp') is not null)
    drop table #temp

If(OBJECT_ID('tempdb..#results') is not null)
    drop table #results

    If(OBJECT_ID('tempdb..#pre') is not null)
    drop table #pre

create table #results
(
Student_id int, course varchar(20)
)
create table #pre
(
course varchar(10),
prerequisite_course varchar(10)
)


Declare @count int,@startcount int=1,@studentid int

select ROW_NUMBER() over (order by student_id asc) as RN,* into #temp from #applications
select @count=count(*) from #temp



while(@startcount<=@count)
begin
set @studentid= (select student_id from #temp where RN=@startcount)


;with cte1 as
(
select * from #prerequisites where course=(select top 1 b.prerequisite_course from 
#applications a
join #prerequisites b on a.requested_course=b.course
where student_id=@studentid)
UNION ALL
select b.prerequisite_course,b.course from #prerequisites b
join cte1 on cte1.course=b.course
)
insert into #pre
select * from cte1

insert into #results
select * from #applications where student_id=@studentid
union 
select ISNULL(a.student_id,@studentid),#pre.prerequisite_course
from 
#pre
left join #register a on a.registered=#pre.prerequisite_course and a.student_id=@studentid
where a.student_id is null 
set @startcount=@startcount+1
end
select * from #results

输出

Student_id  course
1   ML
1   MTH
2   CMP
2   HS
3   CMP
3   HS
3   ML
3   MTH