22,207
社区成员
发帖
与我相关
我的任务
分享
select * from answer where [questions.id] in (select top 4 id from questions order by newid())
把找到的ID先插入一个临时表,然后从临时表里面取ID
select top 4 id into tmp from questions order by newid()
(--insert into tmp(id) select top 4 id from questions order by newid())
select * from answer where [questions.id] in (select id from tmp)
drop table tmp(--delete from tmp)
select top 10 a.*,b.* from @questions a join @answers b on a.id=b.Qid where 正确答案=1 order by newid()
select a.*,b.* from questions a left join answers b on a.id=b.Qid
where a.id=(select top 1 id from questions order by newid())
declare @id as int
set @id = (select top 1 id from questions order by newid())
select * from questions where id = @id
select * from answers where [questions.id] = @id
--参考:
declare @questions table(id int,题目 varchar(20),输入人 int,类型 int,时间 datetime)
insert @questions select 1,'题目1',884,1,'2009-03-09 18:52:40.500'
insert @questions select 2,'题目2',884,1,'2009-03-09 18:52:40.513'
declare @answers table(id int,答案 varchar(10),选项 varchar(10),正确答案 varchar(20),输入人 int,Qid int,时间 datetime)
insert @answers select 1,'答案1','A', 0, 884, 1, '2009-03-09 18:53:04.123'
insert @answers select 2,'答案2','B', 1, 884, 1, '2009-03-09 18:53:04.123'
insert @answers select 3,'答案3','C', 0, 884, 1, '2009-03-09 18:53:04.123'
insert @answers select 4,'答案4','D', 0, 884, 1, '2009-03-09 18:53:04.123'
insert @answers select 5,'答案1','A', 0, 884, 2, '2009-03-09 18:53:20.233'
insert @answers select 6,'答案2','B', 0, 884, 2, '2009-03-09 18:53:20.233'
insert @answers select 7,'答案3','C', 0, 884, 2, '2009-03-09 18:53:20.233'
insert @answers select 8,'答案4','D', 1, 884, 2, '2009-03-09 18:53:20.233'
select top 1 a.*,b.* from @questions a join @answers b on a.id=b.Qid where 正确答案=1 order by newid()
--结果(有时第一行,有时第二行):
id 题目 输入人 类型 时间 id 答案 选项 正确答案 输入人 Qid 时间
----------- -------------------- ----------- ----------- ----------------------- ----------- ---------- ---------- -------------------- ----------- ----------- -----------------------
2 题目2 884 1 2009-03-09 18:52:40.513 8 答案4 D 1 884 2 2009-03-09 18:53:20.233
(1 行受影响)
id 题目 输入人 类型 时间 id 答案 选项 正确答案 输入人 Qid 时间
----------- -------------------- ----------- ----------- ----------------------- ----------- ---------- ---------- -------------------- ----------- ----------- -----------------------
1 题目1 884 1 2009-03-09 18:52:40.500 2 答案2 B 1 884 1 2009-03-09 18:53:04.123
(1 行受影响)
select top 1 *
from questions q
left join answers a on a.questions.id=q.id and a.正确答案=1
order by newid()
RAND
返回 0 到1 之间的随机float 值。
rowid()