如何使用我的查询中不存在 [英] How to use not exist in my query
问题描述
考虑我的桌子
表1:assignmentquestion
Consider my tables
table 1: assignmentquestion
select* from assignmentquestion
输出:
Output:
id cid aid Question
27 1 21 what is bca?
28 1 21 what is eee?
29 2 22 what is ba ?
30 2 22 what is aa?
31 4 23 what is bsc?
32 4 23 what is bbb?
35 1 24 what is dddd?
36 1 24 what is ggg?
37 4 23 what is ccc?
38 9 25 what is v?
39 1 21 what is ggg?
40 1 21 what is sas?
41 1 21 What is Inheritance?
表2:assignmentanswer
table 2: assignmentanswer
select * from assignmentanswer
OutPut:
OutPut:
id sid aid ans date
138 1 21 1 2013-01-11 12:49:56.717 27
139 1 21 2 2013-01-11 12:49:57.123 28
140 6 24 ddd1 2013-01-11 12:50:44.610 35
141 6 24 ggg2 2013-01-11 12:50:45.010 36
142 6 21 sd 2013-01-11 15:27:24.427 27
143 6 21 asd 2013-01-11 15:27:24.850 28
144 6 21 fgdfgf 2013-01-11 15:27:25.277 39
145 6 21 dghfgfh 2013-01-11 15:27:25.700 40
我的查询从表中获取最后一条记录学生没有参加这个问题的任务。
My Query to get the last record from table assignmentquestion where the student have not attended the question.
41 1 21 What is Inheritance?
select asm.id,asm.aid,asm.question,asn.id,asn.aid,asn.answer from assignmentquestion asm
inner join assignmentanswer asn on asm.aid=asn.aid and asm.id=asn.qid where asn.sid=6 and asn.aid=21
输出:
id援助问题ID援助ans
27 21什么是bca? 142 21 sd
28 21什么是eee? 143 21 asd
39 21什么是ggg? 144 21 fgdfgf
40 21什么是sas? 145 21 dghfgfh
我的预期输出是获得assignquestion表的最后一条记录:
Output:
id aid Question id aid ans
27 21 what is bca? 142 21 sd
28 21 what is eee? 143 21 asd
39 21 what is ggg? 144 21 fgdfgf
40 21 what is sas? 145 21 dghfgfh
My expected output is to get the last record of assignmentquestion table as:
41 1 21 What is Inheritance?
推荐答案
如果最后一条记录意味着你想要最大的id,那么你可以按相反的顺序排序,并获取TOP 1记录。如下所示:
If the last record means that you want the greatest id, then you can order the set in reverse order and take TOP 1 record. So something like:
select top (1)
asm.id,
asm.aid,
asm.question,
asn.id,
asn.aid,
asn.answer
from assignmentquestion asm
inner join assignmentanswer asn
on asm.aid=asn.aid
and asm.id=asn.qid
where asn.sid = 6
and asn.aid = 21
order by asm.id desc
如果时间很重要,试试这个:
If time is essential, try this:
DECLARE @maxDate DATE
SELECT @maxDate = MAX([date])
FROM assignmentanswer AS asn
WHERE asn.sid=6 and asn.aid=21
SELECT asm.id,asm.aid,asm.question,asn.id,asn.aid,asn.answer
FROM assignmentquestion asm
INNER JOIN assignmentanswer asn on asm.aid=asn.aid and asm.id=asn.qid
WHERE asn.sid=6 and asn.aid=21 and asn.date = @maxDate
嗨这是我的tasksanswer的表结构,昨天存放了questionId,我忘了关于Qid列,请通过它...
Hi this is my table structure for assignmentanswer which stores questionId yesterday i forgot to highlith the Qid column please go through it..
Select * from assignmentanswer
id sid aid ans date Qid
138 1 21 1 2013-01-11 12:49:56.717 27
139 1 21 2 2013-01-11 12:49:57.123 28
140 6 24 ddd1 2013-01-11 12:50:44.610 35
141 6 24 ggg2 2013-01-11 12:50:45.010 36
142 6 21 sd 2013-01-11 15:27:24.427 27
143 6 21 asd 2013-01-11 15:27:24.850 28
144 6 21 fgdfgf 2013-01-11 15:27:25.277 39
145 6 21 dghfgfh 2013-01-11 15:27:25.700 40
146 1 23 gfj 2013-01-12 11:20:18.800 31
147 1 23 gf 2013-01-12 11:20:23.430 32
148 1 23 fg 2013-01-12 11:20:29.257 37
149 1 22 sda 2013-01-12 11:26:40.590 29
150 1 22 ertr 2013-01-12 11:26:40.973 30
这篇关于如何使用我的查询中不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!