如何使用我的查询中不存在 [英] 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屋！