我有这个代码及其临时表，所以你可以操作它。 - }( H) K1 g5 z4 K1 L- Fcreate table #student( id int identity firstname varchar lastname varchar(50))create table #quiz( id int identity quiz_name varchar(50))create table #quiz_details( id int identity quiz_id int, student_id int)insert into #student(firstname,lastname)values ('LeBron','James'),('Stephen','Curry')insert into #quiz(quiz_name)values('NBA 50 Greatest Player Quiz'),('NBA Top 10 3 point shooters')insert into #quiz_details(quiz_id,student_id)values (1，2)drop table #studentdrop table #quizdrop table #quiz_details所以你可以看到勒布朗·詹姆斯（Lebron James）参加了NBA斯蒂芬·库里（Stephen Curry）则获得了NBA ( E4 b3 L+ g) L/ R! K测试50名最佳球员。 . H6 Q4 o2 t- E' C/ K- d我只想得到他们还没有得到的东西，比如勒布朗还没有参加过50个最伟大的球员，所以我想要的就是这样。 , @/ E, P% X- S8 v# B' I' ~0 qid quiz_name firstname lastname----------------------------------------------------1 NBA 50 Greatest Player Quiz NULL NULL我需要两个参数，即lebron的ID和测验的ID，以便我知道lebron或stephen它还没有被接受，但如果值student_id还是空的，我该怎么办？ * _+ j" y9 i! h$ d6 k) F, W我的尝试： / p! J, \: |2 g8 oselect QD.id, Q.quiz_name, S.firstname, S.lastnamefrom #quiz_details QDinner join #quiz Q on Q.id = QD.quiz_idinner join #student S on S.id = QD.student_id 1 u6 e) p+ }4 K5 M 解决方案: 9 N& X' R" {; F, U2 I2 Z- D2 k 这应该让你开始： / Y" J9 @3 r) }, k! \-- filter out the student and quiz you wantDECLARE @qid INT = 1DECLARE @sid INT = 1SELECT * FROM #student AS sINNER JOIN #quiz AS q -- you want the quiz ON 1=1LEFT OUTER JOIN #quiz_details AS qd -- left join here to get result where rows not found ON qd.id = q.id AND qd.student_id=s.idWHERE s.id = @sid AND q.id = @qid AND qd.id IS NULL -- only return quizes not taken
技术问答
385 人阅读
|
0 人回复
|
2023-09-14
