请教一个今天遇到的关于随机出试题的SQL语句
今天去笔试：遇到一个题目，题目大概是这样的：
有一个试题表：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语句该怎么写？
[解决办法]
简单的写应该是这样的，期待更牛逼的算法。
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()
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]=1
print @count1
print @count2
if (@count1=8 and @count2=13)
begin
set @match=1
break
end
end
select * from #temp
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=1
while @count<20
begin
begin 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 #tb
end
--查看结果
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
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 行受影响)
*/
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
