数据库：mssql 2000题库表：tmdm(题目代码,主键),tmmc(题目名称),Amc(选项A内容),Bmc(选项B内容),Cmc(选项C内容),Dmc(选项D内容),A(bit型),B(bit型),C(bit型),D(bit型),FenShu(分数)试卷表：SJzdbm(试卷自动编码，主键),xh(学生学号),tmdm(题目代码，外键),A1(学生答案A，bit型),B1(学生答案B),C1(学生答案C),D1(学生答案D),A(标准答案A),B(标准答案B),C(标准答案C),D(标准答案D),FenShu(分数)
现在题库表里面有几千道题目，分值都是不定的，学生登录后，随机不重复地生成个人试题，最后试卷的总分值可以超出100一点点，但试题一定要是随机提取的，并且是不能重复的。本人正在开发一个比较简单的在线考试系统，有以上经验的同志请指点指点，谢谢~~
现在题库表里面有几千道题目，分值都是不定的，学生登录后，随机不重复地生成个人试题，最后试卷的总分值可以超出100一点点，但试题一定要是随机提取的，并且是不能重复的。本人正在开发一个比较简单的在线考试系统，有以上经验的同志请指点指点，谢谢~~
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-30 04:18:12
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([tmdm] int,[tmmc] nvarchar(4),[Amc] nvarchar(3),[Bmc] nvarchar(3),[Cmc] nvarchar(3),[Dmc] nvarchar(3),[A] int,[B] int,[C] int,[D] int,[FenShu] int)
Insert tb
Select 1,'tm1','a1','b1','c1','d1',0,0,0,1,10 union all
Select 2,'tm2','a2','b2','c2','d2',0,0,1,1,11 union all
Select 3,'tm3','a3','b3','c3','d3',0,1,0,0,12 union all
Select 4,'tm4','a4','b4','c4','d4',0,1,0,0,13 union all
Select 5,'tm5','a5','b5','c5','d5',0,1,0,0,14 union all
Select 6,'tm6','a6','b6','c6','d6',0,1,0,0,15 union all
Select 7,'tm7','a7','b7','c7','d7',0,1,0,0,16 union all
Select 8,'tm8','a8','b8','c8','d8',0,1,0,0,17 union all
Select 9,'tm9','a9','b9','c9','d9',0,0,0,1,18 union all
Select 10,'tm10','a10','b10','c10','d10',0,0,0,1,19 union all
Select 11,'tm11','a11','b11','c11','d11',0,0,0,1,20 union all
Select 12,'tm12','a12','b12','c12','d12',0,0,0,1,21 union all
Select 13,'tm13','a13','b13','c13','d13',0,0,0,1,22 union all
Select 14,'tm14','a14','b14','c14','d14',0,0,0,1,23 union all
Select 15,'tm15','a15','b15','c15','d15',0,0,0,1,24 union all
Select 16,'tm16','a16','b16','c16','d16',0,0,0,1,25 union all
Select 17,'tm17','a17','b17','c17','d17',0,0,0,1,26 union all
Select 18,'tm18','a18','b18','c18','d18',0,0,0,1,27 union all
Select 19,'tm19','a19','b19','c19','d19',0,0,0,1,28 union all
Select 20,'tm20','a20','b20','c20','d20',0,0,0,1,29 union all
Select 21,'tm21','a21','b21','c21','d21',0,0,0,1,30 union all
Select 22,'tm22','a22','b22','c22','d22',0,0,0,1,31 union all
Select 23,'tm23','a23','b23','c23','d23',0,0,0,1,32 union all
Select 24,'tm24','a24','b24','c24','d24',0,0,0,1,33 union all
Select 25,'tm25','a25','b25','c25','d25',0,0,0,1,34 union all
Select 26,'tm26','a26','b26','c26','d26',0,0,0,1,35 union all
Select 27,'tm27','a27','b27','c27','d27',0,0,0,1,36 union all
Select 28,'tm28','a28','b28','c28','d28',0,0,0,1,37 union all
Select 29,'tm29','a29','b29','c29','d29',0,0,0,1,38 union all
Select 30,'tm30','a30','b30','c30','d30',0,0,0,1,39
Go
--Select * from tb-->SQL查询如下:
select id=identity(int),* into # from tb order by newid()
select *
from # t
where isnull((select sum(fenshu) from # where id<t.id),0)<=100
/*
id tmdm tmmc Amc Bmc Cmc Dmc A B C D FenShu
----------- ----------- ---- ---- ---- ---- ---- ----------- ----------- ----------- ----------- -----------
1 14 tm14 a14 b14 c14 d14 0 0 0 1 23
2 5 tm5 a5 b5 c5 d5 0 1 0 0 14
3 13 tm13 a13 b13 c13 d13 0 0 0 1 22
4 27 tm27 a27 b27 c27 d27 0 0 0 1 36
5 11 tm11 a11 b11 c11 d11 0 0 0 1 20(5 行受影响)
*/
drop table #
试卷表：tb_ShiJuan