有一个试题表: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语句该怎么写?
参考解:
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 int
set @type1=1
set @type2=2
set @type3=3
set @difficulty1=1
set @difficulty2=2
set @distinguish1=1
set @distinguish2=2
declare @i int
set @i = 1
while (@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
end
select * from #t
(注:它这里的随机主要依靠函数newid())