请教一个今天遇到的关于随机出试题的SQL语句

Question

今天去笔试：遇到一个题目，题目大概是这样的：
有一个试题表：T_EXAM {ID,type(1,2,3/表示试题类型)，difficulty(1,2/试题难度)，distinguish(1,2/区分)}
现在我要从题库随机抽出20道题，type：类型1的6道，2的7道，3的7道；difficulty：难度1的8道，2的12道；distinguish：区分1的13道，2的7道：请问能用SQL查询出来吗？
如果能用SQL查询出来，SQL语句该怎么写？

Answer 1

考的是用 top(或rownumber())\ order by  \union all\  的运用

Answer 2

简单的写应该是这样的，期待更牛逼的算法。
select top 6 * from tb  where type=1 and difficulty=1 and distinguish=1 oder by newid()
union all
select top 2 * from tb  where type=2 and difficulty=1 and distinguish=1 oder by newid()
union all
select top 5 * from tb  where type=2 and difficulty=2 and distinguish=1 oder by newid()
union all
select top 7 * from tb  where type=2 and difficulty=2 and distinguish=2 oder by newid()

Answer 3

if object_id('T_EXAM') is not null
begin 
drop table [T_EXAM]
end CREATE TABLE [dbo].[T_EXAM](
[ID] [int],
[type] [int] NULL,
[difficulty] [int] NULL,
[distinguish] [int] NULL
) ON [PRIMARY]
insert into [T_EXAM]([ID],[type],[difficulty],[distinguish])
select top 10000 row_number()over(order by newid()), abs(checksum(newid())%3)+1,abs(checksum(newid())%2)+1,abs(checksum(newid())%2)+1 from master.dbo.spt_values a 
declare @match int
declare @count1 int
declare @count2 int
set @match=0
while (@match<>1)
begin if object_id('tempdb..#temp') is not null
drop table #temp select top 6 * into #temp from T_EXAM
where type=1
order by newid() insert into #temp
select top 7 * from T_EXAM
where type=2
order by newid() insert into #temp
select top 7 * from T_EXAM
where type=3
order by newid()
/*8*/
select @count1=count(*) from #temp
where [difficulty]=1
/*13*/
select @count2=count(*) from #temp
where [distinguish]=1print @count1
print @count2
if (@count1=8 and @count2=13)
begin 
set @match=1
break
endend select * from #temp

Answer 4

随机取6条type1 7条type2 7条type3
然后判断是否满足diff=1的有8条。dis=1的有13条。如果有终止循环然后返回20条记录。不然重新随机去20条记录计算。
缺点是while循环要指定一个次数，不然如果没有满足的结果集。会变成死循环。
很傻的算伐。

Answer 5

 create table #tb(
id int  ,
[type] int,
[difficurity] int,
[distinguish] int);

insert into #tb  select top 1 * from T_EXAM order by NEWID()declare @count int=1while @count<20
beginbegin tran
insert into #tb select top 1 * From T_EXAM 
order by NEWID()
if 6 <(select  COUNT(*) from #tb where [TYPE]=1) 
or 7 <(select  COUNT(*) from #tb where [TYPE]=2)
or 7 <(select  COUNT(*) from #tb where [TYPE]=3)
or 8 <(select  COUNT(*) from #tb where [difficurity]=1)
or 12<(select  COUNT(*) from #tb where [difficurity]=2)
or 13<(select  COUNT(*) from #tb where [distinguish]=1)
or 7 <(select  COUNT(*) from #tb where [distinguish]=2)
or exists( select * From #tb group by id having COUNT(*)>1)
rollback tran
else
commit tran

select @count=COUNT(*)From #tbend--查看结果
select [TYPE],count(*) From #tb 
group by [TYPE]select [difficurity],count(*) From #tb 
group by [difficurity]select [distinguish],COUNT(*) From #tb 
group by [distinguish]select * From #tb order by [TYPE],[difficurity],[distinguish]drop table #tb
  

Answer 6

可以把 第一个 
insert into #tb  select top 1 *...
中的top 1 改成  top (n)  
n=所有条件中的最小数，这里是6，这样可以快一些。

Answer 7

SQL code
select top 6 * from tb where type=1 and difficulty=1 and distinguish=1 oder by newid()
union all
select top 2 * from tb where type=2 and difficulty=1 and distinguish……

Answer 8

SQL code
select top 6 * from tb where type=1 and difficulty=1 and distinguish=1 oder by newid()
union all
select top 2 * from tb where type=2 and difficulty=1 and distinguish……

Answer 9

10000题里面随便抽，满足退出，不满足再抽过，花了27秒抽中create table #ta(ID int , type int,difficulty int, distinguish int)
while 1 = 1
begin
insert into #ta
select top(20)* from T_EXAM order by  newid()
if exists(select sum(1) from #ta 
having 
sum(case when type = 1 then 1 else 0 end) = 6 and
sum(case when type = 2 then 1 else 0 end) = 7 and
sum(case when type = 3 then 1 else 0 end) = 7 and
sum(case when difficulty = 1 then 1 else 0 end) = 8 and
sum(case when difficulty = 2 then 1 else 0 end) = 12 and
sum(case when distinguish = 1 then 1 else 0 end) = 13 and
sum(case when distinguish = 2 then 1 else 0 end) = 7)
break
truncate table #ta
end 
select * from #ta
/*
ID          type        difficulty  distinguish
----------- ----------- ----------- -----------
4297        2           2           1
9304        3           2           2
7774        1           2           1
3043        1           1           2
764         2           2           1
4727        3           2           2
9248        1           1           1
4137        3           1           2
3594        2           2           1
2234        3           2           1
7964        1           2           1
2883        1           2           1
3694        2           1           1
4097        3           2           2
2638        3           1           2
4635        3           2           2
1736        1           1           1
7866        2           2           1
1937        2           1           1
5590        2           1           1(20 行受影响)*/

Answer 10

有点迷惑，楼主的问题和我现在做的组卷系统很像，我用mfc和aql server2005也是从数据库中抽题，用的是遗传算法，我的思路和19楼挺像，就是先抽到这个题然后对其他几个条件进行判断，因为如果直接抽符合条件的话是不是要用很多主键（可能对数据库不是很了解）

Answer 11

学习了。大牛 。10000题里面随便抽，满足退出，不满足再抽过，花了27秒抽中SQL code
create table #ta(ID int , type int,difficulty int, distinguish int)
while 1 = 1
begin 
insert into #ta
select top(20)* from T_EXAM order by ……

Answer 12

笔试遇到的题目其实不是这样，不过大概意思是这样的。我开始的时候，也是想先把type=1的6个题抽出保留，然后根据条件抽剩下的题目：判断是否满足条件，如果满足就留下；如果不满足，继续抽下一个题！

Answer 13

路过学习以下，这个题目的理解 个人认为是 每个条件都是独立，试题中并没有说是并且的关系。简单的是每个条件写一个查询之后用 union all 连起来。不过这样子有一个小问题就是有可能有重复的。

Answer 14

学习   知道了newid()

Answer 15

临时表 #t1
id   type
1,1
2,1
3,1
...
20,3
(1的6道，2的7道，3的7道) 
临时表 #t2
id  difficulty  
1,1
2,1
...
20,2
(难度1的8道，2的12道)
临时表 #t3
id  distinguish
1,1
2,1
...
20,2
(1的13道，2的7道)declare #Result 
(
    [ID] [int],
    [type] [int] NULL,
    [difficulty] [int] NULL,
    [distinguish] [int] NULL
)declare @i int
set @i = 1
while (@i <= 20)
begin
  insert into #Result (ID,type,difficulty,distinguish)
  select top 1 ID,type,difficulty,distinguish
  from   T_EXAM t1
  where  type = (select type from #t1 where id=@i)
         and difficulty = (select difficulty from #t2 where id=@i)
         and distinguish= (select distinguish from #t3 where id=@i)
         and not exists(select * from #Result r where r.id=t1.id)  
  order by newid()  set @i = @i + 1
endselect * from #Result

Answer 16

引用 2 楼 的回复:
简单的写应该是这样的，期待更牛逼的算法。SQL code
select top 6 * from tb where type=1 and difficulty=1 and distinguish=1 oder by newid()
union all
select top 2 * from tb where type=2 and difficulty=1 and distingu……
有点不明白为什么这样写，那样不是有很多种组合！

Answer 17

order by newid()
发现一个问题就是会全表扫描，如果数据量大的话不可取。好像有个函数是抽样的，可以抽样数据出来，这个可以试一下，效率应该有很大的提升。

Answer 18

10楼最优，但防止下别取重复就行。
应改为：
    insert into #tb select top 1 * From T_EXAM 
    where id not in (select id from #tb)
    order by NEWID()

Answer 19

// 完全参数处理
create table #t1(id int,type int)
create table #t2(id int,difficulty int)
create table #t3(id int,distinguish int)
declare @type1 int
set @type1 = 6
declare @type2 int
set @type2 = 7
declare @type3 int
set @type3 = 7declare @i int
set @i = 1
while  (@i <= @type1 ) 
begin
  insert into #t1(id ,int) values(@i, 1)
  set @i = @i + 1
end
while  (@i <= @type1 + @type2 ) 
begin
  insert into #t1(id ,int) values(@i, 2)
  set @i = @i + 1
end
while  (@i <= @type1 + @type2 + @type3  ) 
begin
  insert into #t1(id ,int) values(@i, 3)
  set @i = @i + 1
end//#t2, #t3同样处理declare table #Result  
(
  [ID] [int],
  [type] [int] NULL,
  [difficulty] [int] NULL,
  [distinguish] [int] NULL
)
set @i = 1
while (@i <= 20)
begin
  insert into #Result (ID,type,difficulty,distinguish)
  select top 1 ID,type,difficulty,distinguish
  from T_EXAM t1
  where type = (select type from #t1 where id=@i)
  and difficulty = (select difficulty from #t2 where id=@i)
  and distinguish= (select distinguish from #t3 where id=@i)
  and not exists(select * from #Result r where r.id=t1.id)   
  order by newid()  set @i = @i + 1
endselect * from #Result

Answer 20

2楼人才啊，oracle的看行不
select * from (select t.*,ROW_NUMBER() OVER(ORDER BY dbms_random.value(1,99999999)) as rNum from tb t where type=1 and difficulty=1 and distinguish=1) where rNum < 7
union all 
select * from (select t.*,ROW_NUMBER() OVER(ORDER BY dbms_random.value(1,99999999)) as rNum from tb t where type=2 and difficulty=1 and distinguish=1) where rNum < 3
union all
select * from (select t.*,ROW_NUMBER() OVER(ORDER BY dbms_random.value(1,99999999)) as rNum from tb t where type=2 and difficulty=2 and distinguish=1) where rNum < 6
union all
select * from (select t.*,ROW_NUMBER() OVER(ORDER BY dbms_random.value(1,99999999)) as rNum from tb t where type=2 and difficulty=2 and distinguish=2) where rNum < 8
;

Answer 21

重复已经ROLLBACK了。

Answer 22

从现实考虑，题库有几万记录几乎顶天了，10楼的方案不怕题库大而是怕题库小，题库越小ROLLBACK的几率越大，性能是随机的。

Answer 23

应该添加rollback计数，如果题库不够大，有可能永远无法insert最后1条或几条（即使绝对可以抽出这样的20题，但前面的分配错了）。

Answer 24

6+2+xx+xx=20 提取20条记录对吧
咱可以这样么：
数据库表里 一般都有编号对吧 
如果 有1001条记录
随即1001 里20个数 不重复的 然后 去数据表里查可以么 我个人看法 等待高人指点 谢谢

Answer 25

有TOP运算符怎么会全表扫描呢。。OMG

Answer 26

sql有待学习的还是有很多，继续努力的向各位学习

Answer 27

create table #t(
    id int  ,
    [type] int,
    [difficurity] int,
    [distinguish] int);
declare @type1 int
declare @type2 int
declare @type3 int
declare @difficulty1 int
declare @difficulty2 int
declare @distinguish1 int
declare @distinguish2 intset @type1=1
set @type2=2
set @type3=3
set @difficulty1=1
set @difficulty2=2
set @distinguish1=1
set @distinguish2=2declare @i int
set @i = 1while (@i <= 20)
begin   if  (select  COUNT(*) from #tb where [TYPE]=1)  = 6 
       set @type1 = 0
   if  (select  COUNT(*) from #tb where [TYPE]=2)  = 7 
       set @type2 = 0
   if  (select  COUNT(*) from #tb where [TYPE]=3)  = 7 
       set @type3 = 0
   if  (select  COUNT(*) from #tb where [difficurity]=1) = 8
       set @difficulty1=0
   if  (select  COUNT(*) from #tb where [difficurity]=2) = 12
       set @difficulty2=0
   if  (select  COUNT(*) from #tb where [distinguish]=1) = 13
       set @distinguish1=0
   if  (select  COUNT(*) from #tb where [distinguish]=2) = 7
       set @distinguish2=0   insert into #t 
   select top 1 * 
   from    T_EXAM te
   where   [TYPE] in (@type1,@type2,@type3)
       and [difficurity] in (@difficulty1,@difficulty2)
       and [distinguish] in (@distinguish1,@distinguish2)
       and not exists(select * from #t t2 where te.[id]=t2.[id])
   order by newid()   if @@RowCount = 0 //已没有符合记录
      break; 
   else
     set @i = @i + 1
endselect * from  #t

Answer 28

--我也写了一个，真正的随机
WHILE 1=1
BEGIN
--按每20条随机分组
SELECT GID=(ROW_NUMBER() OVER(ORDER BY NEWID())-1)/20,* INTO #t FROM T_EXAM --把符合要求的分组筛选出来
SELECT GID INTO #t2 FROM #t GROUP BY GID HAVING COUNT(*) = 20 
AND SUM(CASE WHEN [type] = 1 THEN 1 ELSE 0 END) = 6
AND SUM(CASE WHEN [type] = 2 THEN 1 ELSE 0 END) = 7
AND SUM(CASE WHEN [type] = 3 THEN 1 ELSE 0 END) = 7
AND SUM(CASE WHEN [difficurity] = 1 THEN 1 ELSE 0 END) = 8
AND SUM(CASE WHEN [difficurity] = 2 THEN 1 ELSE 0 END) = 12
AND SUM(CASE WHEN [distinguish] = 1 THEN 1 ELSE 0 END) = 13
AND SUM(CASE WHEN [distinguish] = 2 THEN 1 ELSE 0 END) = 7

--如果找到了，就结束
IF @@ROWCOUNT > 0
BEGIN
SELECT b.ID,b.[type],b.[difficurity],b.[distinguish] FROM #t2 a JOIN #t b ON a.GID = b.GID ORDER BY a.GID
BREAK
END

--如果没找到，就再来，呵呵

DROP TABLE #t
DROP TABLE #t2
END

Answer 29

10L，85L解法都不错，85L的更好一点。。