89.随机出题的案例

谁借莪１个温暖的怀抱￠ 2024-02-19 18:17 4阅读 0赞

--题库表结构
    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
    --*/