6.1.6 随机出题的示例.sql

sqlserver 数据库编程的绝好脚本

--题库表结构
CREATE TABLE tb(
ID int PRIMARY KEY, --题目ID
Type int,           --题型
col1 varchar(10))   --其他需要的字段
INSERT tb SELECT 1,1,'试题1'
UNION ALL SELECT 2,1,'试题2'
UNION ALL SELECT 3,3,'试题3'
UNION ALL SELECT 4,3,'试题4'
UNION ALL SELECT 5,3,'试题5'
UNION ALL SELECT 6,3,'试题6'
GO

--生成试卷的处理过程
CREATE PROC p_test
@试卷份数 int,
@试题数   varchar(100)
AS
SET NOCOUNT ON
--参数检测
IF ISNULL(@试卷份数,0)<1 RETURN
IF ISNULL(@试题数,'')='' RETURN

--分拆题型
DECLARE @i int,@Type int
SET @i=LEN(@试题数)
SET ROWCOUNT @i
SELECT ID=IDENTITY(int) INTO # FROM sysobjects a,sysobjects b
SET ROWCOUNT 0
CREATE TABLE #type(Type int,Nums int)
INSERT #type(Type,Nums) 
SELECT LEFT(s,CHARINDEX(':',s+':')-1),STUFF(s,1,CHARINDEX(':',s+':'),'')
FROM(
	SELECT s=SUBSTRING(@试题数,ID,CHARINDEX(',',@试题数+',',ID)-ID)
	FROM #
	WHERE SUBSTRING(','+@试题数,ID,1)=','
)a WHERE ISNUMERIC(LEFT(s,CHARINDEX(':',s+':')-1))=1
	AND ISNUMERIC(STUFF(s,1,CHARINDEX(':',s+':'),''))=1
	AND STUFF(s,1,CHARINDEX(':',s+':'),'')>0
	AND EXISTS(
		SELECT * FROM tb WHERE Type=LEFT(s,CHARINDEX(':',s+':')-1))

--使用游标,为每种题型随机抽取题目
CREATE TABLE #t(gID int IDentity,type int,ID int)
DECLARE tb CURSOR LOCAL
FOR
SELECT Type,Nums*@试卷份数 FROM #Type
OPEN tb
FETCH tb INTO @Type,@i
WHILE @@FETCH_STATUS=0
BEGIN
	--为每个题型随机抽取题目
	SET ROWCOUNT @i
	INSERT #t(Type,ID) SELECT @Type,ID FROM tb 
	WHERE Type=@Type ORDER BY NEWID()
	SET @i=@i-@@ROWCOUNT
	WHILE @i>0
	BEGIN
		SET ROWCOUNT @i
		INSERT #t(Type,ID) SELECT @Type,ID FROM tb 
		WHERE Type=@Type ORDER BY NEWID()
		SET @i=@i-@@ROWCOUNT
	END
	FETCH tb INTO @Type,@i
END
CLOSE tb
DEALLOCATE tb

--显示结果
SET ROWCOUNT 0
SELECT 试卷编号=(b.gid-b1.gid)/c.Nums+1
	,a.*
FROM tb a,#t b,#type c
	,(SELECT Type,gid=MIN(gid) FROM #t GROUP BY Type)b1
WHERE a.ID=b.ID 
	AND b.Type=c.Type
	AND b.Type=b1.Type
ORDER BY 试卷编号,b.gid
GO

--调用
EXEC p_test 2,'1:1,3:2'
/*--结果之一
试卷编号    ID          Type        col1 
---------------- ------------------ ----------------- ---------- 
1           2           1           试题2
1           6           3           试题6
1           3           3           试题3
2           1           1           试题1
2           5           3           试题5
2           4           3           试题4
--*/