请教如何随机记录
要求：避免抽取的记录集中在某一范围，尽可能分散.
比如下表：
题目ID 章节 知识点
1 1 1
2 1 1
3 1 1
4 1 2
5 1 2
6 1 2
7 1 3
8 1 3
9 1 3
10 2 1
11 2 1
12 2 1
13 2 2
14 2 2
15 2 2
16 2 3
17 2 3
18 2 3
19 3 1
20 3 1
21 3 1
22 3 2
23 3 2
24 3 2
25 3 3
26 3 3
27 3 3
……
从上表随机抽取10个题目记录，并且避免集中在某一章节、知识点范围内，尽量能分散。
------解决思路----------------------
兼顾各个章节各个知识点随机
IF OBJECT_ID('TempDB..#TB20141224')IS NOT NULL
DROP TABLE #TB20141224
SELECT *,0 TIMES,0 ISUSED INTO #TB20141224 FROM TB
DECLARE @TABLE TABLE(
[题目ID]INT
,章节 INT
,知识点 INT
,ID INT
)
DECLARE @COUNT INT
SET @COUNT=1
WHILE @COUNT<=10
BEGIN
INSERT INTO @TABLE
SELECT TOP 1 [题目ID],章节,知识点,@COUNT
FROM #TB20141224
WHERE ISUSED=0
ORDER BY TIMES,NEWID()
UPDATE T1
SET TIMES=T1.TIMES+1,ISUSED=(CASE WHEN T1.[题目ID]=T2.[题目ID] THEN 1 ELSE T1.ISUSED END)
FROM #TB20141224 T1
JOIN @TABLE T2 ON T1.章节=T2.章节 OR T1.知识点=T2.知识点
WHERE T2.ID=@COUNT
SET @COUNT=@COUNT+1
END
SELECT * FROM @TABLE