sql2005中，随机获取集合的问题

Question

表名：table
编号 分数
1    10
2    5
3    15
4    5
5    10
6    5
7    5
我如何通过sql查询语句或者存储过程
随机查询
前N条sum(分数)为20的id集合
比如ID集合为：
1,5
1，2，4
2，3

Answer 1

其实这个用游标或者while循环写肯定能写出来。但是效率比较低。
所以求效率高点的。

Answer 2

先完成排列组合,
然后inner join原先的表做sum运算,
等于20的取出..

Answer 3

如果比较难的话，可以降低难度：top N为固定值也行。

Answer 4

能否写成sql语句观摩一下。谢谢

Answer 5

create table tb(id int,val int)
insert into tb select 1,10
insert into tb select 2,5
insert into tb select 3,15
insert into tb select 4,5
insert into tb select 5,10
insert into tb select 6,5
insert into tb select 7,5
go
select a.id id1,b.id id2,(case when a.val+b.val=20 then null else c.id end)id3,a.val v1,b.val v2,(case when a.val+b.val=20 then null else c.val end)v3 from tb a,tb b,tb c where a.id<b.id and b.id<c.id and (a.val+b.val=20 or a.val+b.val+c.val=20)
/*
id1         id2         id3         v1          v2          v3
----------- ----------- ----------- ----------- ----------- -----------
1           2           4           10          5           5
1           2           6           10          5           5
1           2           7           10          5           5
1           4           6           10          5           5
1           4           7           10          5           5
1           5           NULL        10          10          NULL
1           5           NULL        10          10          NULL
1           6           7           10          5           5
2           3           NULL        5           15          NULL
2           3           NULL        5           15          NULL
2           3           NULL        5           15          NULL
2           3           NULL        5           15          NULL
2           4           5           5           5           10
2           5           6           5           10          5
2           5           7           5           10          5
3           4           NULL        15          5           NULL
3           4           NULL        15          5           NULL
3           4           NULL        15          5           NULL
3           6           NULL        15          5           NULL
4           5           6           5           10          5
4           5           7           5           10          5
5           6           7           10          5           5(22 行受影响)*/
go
drop table tb

Answer 6

修正:
create table tb(id int,val int)
insert into tb select 1,10
insert into tb select 2,5
insert into tb select 3,15
insert into tb select 4,5
insert into tb select 5,10
insert into tb select 6,5
insert into tb select 7,5
go
select distinct a.id id1,b.id id2,(case when a.val+b.val=20 then null else c.id end)id3,a.val v1,b.val v2,(case when a.val+b.val=20 then null else c.val end)v3 from tb a,tb b,tb c where a.id<b.id and b.id<c.id and (a.val+b.val=20 or a.val+b.val+c.val=20)
/*
id1         id2         id3         v1          v2          v3
----------- ----------- ----------- ----------- ----------- -----------
1           2           4           10          5           5
1           2           6           10          5           5
1           2           7           10          5           5
1           4           6           10          5           5
1           4           7           10          5           5
1           5           NULL        10          10          NULL
1           6           7           10          5           5
2           3           NULL        5           15          NULL
2           4           5           5           5           10
2           5           6           5           10          5
2           5           7           5           10          5
3           4           NULL        15          5           NULL
3           6           NULL        15          5           NULL
4           5           6           5           10          5
4           5           7           5           10          5
5           6           7           10          5           5(16 行受影响)
*/
go
drop table tb

Answer 7

进一步修正:
create table tb(id int,val int)
insert into tb select 1,10
insert into tb select 2,5
insert into tb select 3,15
insert into tb select 4,5
insert into tb select 5,10
insert into tb select 6,5
insert into tb select 7,5
go
select top 5 * from(
select distinct a.id id1,b.id id2,(case when a.val+b.val=20 then null else c.id end)id3,a.val v1,b.val v2,(case when a.val+b.val=20 then null else c.val end)v3 from tb a,tb b,tb c where a.id<b.id and b.id<c.id and (a.val+b.val=20 or a.val+b.val+c.val=20)
)t order by newid()
/*
id1         id2         id3         v1          v2          v3
----------- ----------- ----------- ----------- ----------- -----------
1           2           6           10          5           5
4           5           7           5           10          5
2           5           7           5           10          5
1           2           7           10          5           5
4           5           6           5           10          5(5 行受影响)*/
go
drop table tb

Answer 8

DECLARE @TB TABLE([编号] INT, [分数] INT)
INSERT @TB 
SELECT 1, 10 UNION ALL 
SELECT 2, 5 UNION ALL 
SELECT 3, 15 UNION ALL 
SELECT 4, 5 UNION ALL 
SELECT 5, 10 UNION ALL 
SELECT 6, 5 UNION ALL 
SELECT 7, 5;WITH CTE AS
(
SELECT *, TOTAL=[分数], CAST([编号] AS VARCHAR(8000)) AS COL FROM @TB
UNION ALL
SELECT T.*, TOTAL+T.[分数], COL+','+CAST(T.[编号] AS VARCHAR(8000)) 
FROM @TB AS T, CTE
WHERE CTE.[编号]<T.[编号] AND TOTAL<20 
)
SELECT TOP 10 COL
FROM CTE
WHERE TOTAL=20
ORDER BY COL
/*
COL
--------------
1,2,4
1,2,6
1,2,7
1,4,6
1,4,7
1,5
1,6,7
2,3
2,4,5
2,4,6,7
*/

Answer 9

我提供一点思路吧。看行不行。
你选 select distinct 分数 from tb  也就是看这个表中有多少种数字。
然后再通过逻辑处理找出能组成20的可能 比如现在有 10 9 2 1 这些数字，那么组成20的就有可能是2个10;
1个10,1个9 1个1；20个1...等等方式。最要就是难在这里，这种可能要在程序中去实现。
然后在分组中来随机选取id.比如2个10的，就在分组随机选2个id.还考虑一个的情况 也就是不够数的就不不算，其实在前面第2步中就可以过滤了。比如现在为1分的只有1个id,那么前面的20个1的组成就可以直接去了。- -！貌似有点复杂。

Answer 10

declare @table table (
编号 int,分数 int
)
insert @table select
1, 10
union all select
2, 5
union all select
3, 15
union all select
4, 5
union all select
5, 10
union all select
6, 5
union all select
7, 5
union all select
8, 3
union all select
9, 2
union all select
10, 7
union all select
11, 8;with cte as (
select cast(',' + CAST(编号 as nvarchar(100)) + ',' as nvarchar(100)) as 编号
   ,分数 from @table
union all
select cast(b.编号 + CAST(a.编号 as nvarchar(100)) + ',' as nvarchar(100)) as 编号
   ,a.分数+b.分数 as 分数  from @table a,cte b
   where b.编号 not like '%,' + CAST(a.编号 as nvarchar(100)) + ',%'
   and a.分数+b.分数<= 20
)
select stuff(left(编号,LEN(编号)-1),1,1,'') as 编号
from cte where  分数= 20  
order by 1居然有1144个答案，可能有重复的
数据量太大不要用这个方法

Answer 11

- -!我搞错了。没看到上面写的top 10

Answer 12

如果分数不为int型。为decimal类型。就要报错。
错误信息如下：在递归查询 "CTE" 的列 "TOTAL" 中，定位点类型和递归部分的类型不匹配。

Answer 13

这样好像不重复了declare @table table (
编号 int,分数 int
)
insert @table select
1, 10
union all select
2, 5
union all select
3, 15
union all select
4, 5
union all select
5, 10
union all select
6, 5
union all select
7, 5
union all select
8, 3
union all select
9, 2
union all select
10, 7
union all select
11, 8;with cte as (
select cast(',' + CAST(编号 as nvarchar(100)) + ',' as nvarchar(100)) as 编号1,编号
   ,分数 from @table
   where 分数<= 20
union all
select cast(b.编号1 + CAST(a.编号 as nvarchar(100)) + ',' as nvarchar(100)) as 编号1,a.编号 
   ,a.分数+b.分数 as 分数  from @table a,cte b
   where a.分数+b.分数<= 20
   and a.编号 > b.编号 
)
select stuff(left(编号1,LEN(编号1)-1),1,1,'') as 编号
from cte where  分数= 20  
order by 1--结果
编号
1,2,4
1,2,6
1,2,7
1,2,8,9
1,4,6
1,4,7
1,4,8,9
1,5
1,6,7
1,6,8,9
1,7,8,9
1,8,10
1,9,11
2,10,11
2,3
2,4,5
2,4,6,7
2,4,6,8,9
2,4,7,8,9
2,4,8,10
2,4,9,11
2,5,6
2,5,7
2,5,8,9
2,6,7,8,9
2,6,8,10
2,6,9,11
2,7,8,10
2,7,9,11
3,4
3,6
3,7
3,8,9
4,10,11
4,5,6
4,5,7
4,5,8,9
4,6,7,8,9
4,6,8,10
4,6,9,11
4,7,8,10
4,7,9,11
5,6,7
5,6,8,9
5,7,8,9
5,8,10
5,9,11
6,10,11
6,7,8,10
6,7,9,11
7,10,11
8,9,10,11
   

Answer 14

当table表数据量大于1000后。查询效率是悲剧的。

Answer 15

;WITH CTE AS
(
SELECT *, TOTAL=CAST([分数] AS DECIMAL(18,2)), CAST([编号] AS VARCHAR(8000)) AS COL FROM @TB
UNION ALL
SELECT T.*, CAST(TOTAL+T.[分数]  AS DECIMAL(18,2)), COL+','+CAST(T.[编号] AS VARCHAR(8000)) 
FROM @TB AS T, CTE
WHERE CTE.[编号]<T.[编号] AND TOTAL<20 
)

Answer 16

全部唯一组合:
create table tb(id int,val int)
insert into tb select 1,10
insert into tb select 2,5
insert into tb select 3,15
insert into tb select 4,5
insert into tb select 5,10
insert into tb select 6,5
insert into tb select 7,5
go
select distinct a.id id1,b.id id2,
(case when a.val+b.val>=20 then null else c.id end)id3,
(case when a.val+b.val+c.val>=20 then null else d.id end)id4,
a.val v1,b.val v2,
(case when a.val+b.val>=20 then null else c.val end)v3,
(case when a.val+b.val+c.val>=20 then null else d.val end)v4
from tb a,tb b,tb c,tb d
where b.id>a.id and 
c.id>(case when a.val+b.val>=20 then a.id else b.id end) and 
d.id>(case when a.val+b.val+c.val>=20 then a.id else c.id end) and 
(a.val+b.val=20 or a.val+b.val+c.val=20 or a.val+b.val+c.val+d.val=20)
/*
id1         id2         id3         id4         v1          v2          v3          v4
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           2           4           NULL        10          5           5           NULL
1           2           6           NULL        10          5           5           NULL
1           2           7           NULL        10          5           5           NULL
1           4           6           NULL        10          5           5           NULL
1           4           7           NULL        10          5           5           NULL
1           5           NULL        NULL        10          10          NULL        NULL
1           6           7           NULL        10          5           5           NULL
2           3           NULL        NULL        5           15          NULL        NULL
2           4           5           NULL        5           5           10          NULL
2           4           6           7           5           5           5           5
2           5           6           NULL        5           10          5           NULL
2           5           7           NULL        5           10          5           NULL
3           4           NULL        NULL        15          5           NULL        NULL
3           6           NULL        NULL        15          5           NULL        NULL
3           7           NULL        NULL        15          5           NULL        NULL
4           5           6           NULL        5           10          5           NULL
4           5           7           NULL        5           10          5           NULL
5           6           7           NULL        10          5           5           NULL(18 行受影响)
*/
go
drop table tb

Answer 17

用OPTION (MAXRECURSION 次数)限制递归次数。

Answer 18

这个是递归完了再进行限制的吗？
我看用法是这样的：
SELECT * FROM CTE
OPTION(MAXRECURSION 0)
不知道对不对。

Answer 19

执行语句时限制，不过这样不可以，用top 1。

Answer 20

SQL codecreate table tb(id int,val int)
insert into tb select 1,10
insert into tb select 2,5
insert into tb select 3,15
insert into tb select 4,5
insert into tb select 5,10
insert into tb select 6,5
insert into tb select 7,5
go
select top 5 * from(
select distinct a.id id1,b.id id2,(case when a.val+b.val=20 then null else c.id end)id3,a.val v1,b.val v2,(case when a.val+b.val=20 then null else c.val end)v3 from tb a,tb b,tb c where a.id<b.id and b.id<c.id and (a.val+b.val=20 or a.val+b.val+c.val=20)
)t order by newid()
/*
id1         id2         id3         v1          v2          v3
----------- ----------- ----------- ----------- ----------- -----------
1           2           6           10          5           5
4           5           7           5           10          5
2           5           7           5           10          5
1           2           7           10          5           5
4           5           6           5           10          5(5 行受影响)*/
go
drop table tb

Answer 21

如果查询出来再用order by newid()的话。效率还是很慢。

Answer 22

  top觉得还是不行！ top只能规定了是多少数据 那在不知道table里面的数据时怎么办？   等待各位大神的结果！

Answer 23

递归出来的数据集合总数未知的情况下。是不能top。
那么看能否通过一下方法解决：
1、在递归过程中order by newid()，并且递归到符合条件的第一条记录就退出。
2、通过作业或者其他方式。把所有符合条件的id集合放入表中。（数据量大的时候，很吓人，昨天我曾经造成磁盘空间不足。）。而且这种方法弊端太盛，新增一条记录就得来一次