随机选题例子
[/quo--oracle随机选题例子：
--脚本
CREATE TABLE t_tiku(
QID NUMBER PRIMARY KEY, --题id
QTEXT VARCHAR2(20), --题名字
RIGHT_ANSWER VARCHAR2(10), --正确答案
FEN NUMBER, --小分
QTYPE NUMBER --题型
)
INSERT INTO VALUES(1001,'text1','a',2,1);
INSERT INTO VALUES(1002,'text2','a',2,1);
INSERT INTO VALUES(1003,'text3','a',2,1);
NSERT INTO VALUES(1004,'text4','a',2,1);
INSERT INTO VALUES(1005,'text5','a',2,1);
INSERT INTO VALUES(1006,'text6','a',2,1);
NSERT INTO VALUES(1007,'text7','a',2,1);
INSERT INTO VALUES(1008'text8','a',2,1);
INSERT INTO VALUES(1009,'text9','a',2,1);
INSERT INTO VALUES(1010,'text10','a',2,1);
随机数：dbms_random.value
select dbms_random.value from t_tiku;
VALUE
0.540034772487469
0.286456681521246
0.160893696485119
0.362955498433561
0.524365619045099
0.31189152490327
0.347187727842068
0.973152290070371
0.102302356581682
0.282644332699209
order by 这个随机数
SELECT qid,qtext ,dbms_random.value FROM t_tiku ORDER BY dbms_random.VALUE;
QIDQTEXTVALUE
1009text90.0784931627373549
1006text60.176706781727269
1004text40.231724674763395
1008text80.294310204707983
1005text50.327235915771007
1001text10.462670151206477
1003text30.574868361508014
1002text20.598607107340676
1010text100.598639600520464
1007text70.76203423269984
这样题的顺序就是不固定的
现在想取出前5道题
rownum 关键字
select rownum,qid,qtext from t_tiku
where rownum<=5
order by dbms_random.value;
ROWNUMQIDQTEXT
21002text2
31003text3
11001text1
51005text5
41004text4
不管怎样执行：都是取出前5道题，只是顺序是随机的，这不是我们想要的结果。
知识点：要用到子查询
1：先随机查出全部
select qid,qtext from t_tiku order by dbms_random.value;
QIDQTEXT
1001text1
1008text8
1003text3
1010text10
1004text4
1007text7
1009text9
1005text5
1002text2
1006text6
2：根据这个结果在取出前5个
select rownum ,qid,qtext from
(select qid,qtext from t_tiku order by dbms_random.value)
where rownum <=5;
ROWNUMQIDQTEXT
11010text10
21008text8
31001text1
41002text2
51003text3
为了测试：再执行一次看看结果
ROWNUMQIDQTEXT
11004text4
21005text5
31010text10
41009text9
51006text6te]
当然这个也可以作为分页
select * from (
select b.*,rownum row_num from
(
select * from t_tiku t order by t.qid asc
) b
) tt where tt.row_num between 2 and 5
