三位斑竹、高手：求一个复杂查询的简洁方案

Question

是一下这样：一个循环，每次用
SELECT TOP 40 * INTO #TEMP FROM tProducts ORDER BY NEWID()
再判断是否符合你的条件，不符合就循环取下一次，符合就给列出来。这个思路我试过，速度很慢，半天出不来。希望有人能改进这个思路。

Answer 1

构造表：
create table csdn (id int,f1 char(1),f2 char(1),f3 char(1),f4 char(1))
--加些随机记录进去，随机分布，没有重复值
--将分布标准保存到表中，为了进行验证
create table stand1(f1 char(1) ,stand int)
insert into stand1 values ('1',20)
insert into stand1 values ('2',10)
insert into stand1 values ('3',20)
insert into stand1 values ('4',30)
insert into stand1 values ('5',20)create table stand2(f1 char(1) ,stand int)
insert into stand2 values ('A',30)
insert into stand2 values ('B',20)
insert into stand2 values ('C',40)
insert into stand2 values ('D',10)create table stand3(f1 char(1) ,stand int)
insert into stand3 values ('1',10)
insert into stand3 values ('2',30)
insert into stand3 values ('3',20)
insert into stand3 values ('4',40)
--开始生成随机记录
set nocount on
declare @cnt1 int,@cnt2 int,@cnt3 int,@cnt4 int ,@cnt5 int
--每次调用时，这几个变量换不同的值，使得其和为40，加权和为150
select @cnt1=0
select @cnt2=6
select @cnt3=9
select @cnt4=14
select @cnt5=11declare @str1 char(1000)
select @str1=replicate('1123344455',100)
declare @str2 char(1000)
select @str2=replicate('AAABBCCCCD',100)
declare @str3 char(1000)
select @str3=replicate('1222334444',100)select top 0 f1,f2,f3,f4 into #temp from csdn declare @sql varchar(8000)
select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt1 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
'from (select top 400 substring('''+@str1+''',abs(checksum(newid()))%10+1 ,1) as f1, '+
' substring('''+@str2+''',abs(checksum(newid()))%10+1 ,1) as f2, '+
' substring('''+@str3+''',abs(checksum(newid()))%10+1 ,1) as f3 from csdn ) '+
't0 join csdn t1 on t0.f1=t1.f1 and t0.f2=t1.f2 and t0.f3=t1.f3 where t1.f4=1 '+
'order by newid() ) t3'exec (@sql)select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt2 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
'from (select top 400 substring('''+@str1+''',abs(checksum(newid()))%10+1 ,1) as f1, '+
' substring('''+@str2+''',abs(checksum(newid()))%10+1 ,1) as f2, '+
' substring('''+@str3+''',abs(checksum(newid()))%10+1 ,1) as f3 from csdn ) '+
't0 join csdn t1 on t0.f1=t1.f1 and t0.f2=t1.f2 and t0.f3=t1.f3 where t1.f4=2 '+
'order by newid() ) t3'exec (@sql)select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt3 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
'from (select top 400 substring('''+@str1+''',abs(checksum(newid()))%10+1 ,1) as f1, '+
' substring('''+@str2+''',abs(checksum(newid()))%10+1 ,1) as f2, '+
' substring('''+@str3+''',abs(checksum(newid()))%10+1 ,1) as f3 from csdn ) '+
't0 join csdn t1 on t0.f1=t1.f1 and t0.f2=t1.f2 and t0.f3=t1.f3 where t1.f4=3 '+
'order by newid() ) t3'exec (@sql)select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt4 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
'from (select top 400 substring('''+@str1+''',abs(checksum(newid()))%10+1 ,1) as f1, '+
' substring('''+@str2+''',abs(checksum(newid()))%10+1 ,1) as f2, '+
' substring('''+@str3+''',abs(checksum(newid()))%10+1 ,1) as f3 from csdn ) '+
't0 join csdn t1 on t0.f1=t1.f1 and t0.f2=t1.f2 and t0.f3=t1.f3 where t1.f4=4 '+
'order by newid() ) t3'exec (@sql)select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt5 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
'from (select top 400 substring('''+@str1+''',abs(checksum(newid()))%10+1 ,1) as f1, '+
' substring('''+@str2+''',abs(checksum(newid()))%10+1 ,1) as f2, '+
' substring('''+@str3+''',abs(checksum(newid()))%10+1 ,1) as f3 from csdn ) '+
't0 join csdn t1 on t0.f1=t1.f1 and t0.f2=t1.f2 and t0.f3=t1.f3 where t1.f4=5 '+
'order by newid() ) t3'exec (@sql)--显示查询结果
select * from #temp--验证
select count(*) as cnt,sum(cast(f4 as int)) as sum from #tempselect #temp.f1,count(*)/.4 as rate,stand1.stand 
from #temp join stand1 on #temp.f1=stand1.f1 
group by #temp.f1,stand1.stand order by #temp.f1select #temp.f2,count(*)/.4 as rate,stand2.stand 
from #temp join stand2 on #temp.f2=stand2.f1 
group by #temp.f2,stand2.stand order by #temp.f2select #temp.f3,count(*)/.4 as rate,stand3.stand 
from #temp join stand3 on #temp.f3=stand3.f1 
group by #temp.f3,stand3.stand order by #temp.f3--删除临时表drop table #temp
运行结果：
f1   f2   f3   f4   
---- ---- ---- ---- 
2    A    2    2
4    C    2    2
1    C    3    2
2    A    4    2
3    D    3    2
4    C    4    2
4    B    3    3
1    C    2    3
3    C    4    3
5    D    1    3
1    C    1    3
5    D    4    3
5    C    2    3
3    A    3    3
5    C    3    3
1    C    4    4
4    A    4    4
5    B    4    4
5    B    4    4
3    A    2    4
4    A    4    4
3    A    2    4
5    C    4    4
4    A    3    4
3    A    4    4
5    B    4    4
5    C    4    4
3    A    4    4
1    B    4    4
1    C    4    5
2    A    2    5
2    A    1    5
1    B    4    5
4    C    4    5
4    C    3    5
3    C    4    5
4    C    4    5
3    A    2    5
4    A    2    5
4    C    2    5cnt         sum         
----------- ----------- 
40          150f1   rate                stand       
---- ------------------- ----------- 
1    17.500000           20
2    10.000000           10
3    22.500000           20
4    27.500000           30
5    22.500000           20f2   rate                stand       
---- ------------------- ----------- 
A    35.000000           30
B    15.000000           20
C    42.500000           40
D    7.500000            10f3   rate                stand       
---- ------------------- ----------- 
1    7.500000            10
2    25.000000           30
3    17.500000           20
4    50.000000           40这里有一点要强调：因为你给的样本总体太小了，只有40个，所以不可能在分布上得到很精确的结果。举个例子，按标准40*10%=4，若我取出5个值，5/40=12%，取6个，6/40=15%，取值增长很小一点，精确度就差很多了。所以总体样本是40的时候，以上面的结果来说已经很精确了。
若将总体样本改为400（要求权重和为1500），精确度就更接近标准了。如下：
cnt         sum         
----------- ----------- 
400         1500f1   rate        stand       
---- ----------- ----------- 
1    20          20
2    8           10
3    23          20
4    39          30
5    9           20f2   rate        stand       
---- ----------- ----------- 
A    30          30
B    20          20
C    39          40
D    9           10f3   rate        stand       
---- ----------- ----------- 
1    9           10
2    28          30
3    18          20
4    44          40因为要求记录数为40，权重和为150，所以其实每一次运算后，权重的分布总会是某一个确定的结果，即@cnt1,@cnt2,@cnt3,@cnt4,@cnt5的组合是确定的。
所以在调用这个过程时，先确定下@cnt1,@cnt2,@cnt3,@cnt4,@cnt5的值，只要这几个值满足条件，就用这几个值来分别生成相应个数的记录。
所以还需要用一个算法来算出一组这个值，每次只要算出一个就行了。如果有心情，用穷举法，将所有可能的组合存入一个表里，每次随机取一个记录就更好了。不知道有没有明白我的意思？

Answer 2

今天可以上网的时间少得可怜，没来得及仔细看完各位解答，尤其是 icevi(按钮工厂) 兄的，见谅。To:microlong(微龙)&yonghengdizhen(离散数学(学习中,请勿打扰)) 
   所有的条件精确符合的自然不一定存在，所以“车间”“类型”“要求”的比例是“约”
   我的意思就是在可能已有的结果方案中随机的选取一种,这还有可能.To: Yang_(扬帆破浪) 
  约占20%意思是最好尽可能的接近，但不要偏差太大，应控制在还是17%-23%以内其他的百分数也一样。To: CSDNM(CSDN经理(信就不假) 
  我原来的思路和你差不多，可实际上好象理论上可行，实际上几乎不可用。其实我这个人不到万不得已不会求人，这个问题琢磨了很久了，一直都没一个切实可行的办法，才来麻烦各位。就问题实质而言，是一个复杂条件的查询，要求高效肯定费脑筋，无以为谢，分有的是，决不让大家在分上失望。谢了

Answer 3

呵呵，时间少也不要乱叫！这么多兄你不叫，偏偏要叫icevi(按钮工厂) 兄。

Answer 4

“车间”为1的约占20%，为2的约占10%，为3的约占20%，为4的约占30%，为5的约占20%
“类型”为A的约占30%，为B的约占20%，为C的约占40%，为D的约占10%
“要求”为1的约占10%，为2的约占30%，为3的约占20%，为4的约占40%
可以这样：
40*20%=8 得出“车间”为1记录条数，
40*10%=4 得出“车间”为2记录条数，
40*20%=8 得出“车间”为3记录条数，
....
40*30%=12 得出“类型”为A记录条数,
40*20%=8 得出“类型”为B记录条数,
....
40*10%=4  得出“要求”为1记录条数,
40*30%=12  得出“要求”为2记录条数,
....
然后，用循环做一个简单的排列组合：
“车间”=1 and “类型”= A and “要求”=1   4条
“车间”=1 and “类型”= A and “要求”=2   4条 
“车间”=2 and “类型”= A and “要求”=2   4条 
“车间”=3 and “类型”= B and “要求”=2   4条 
..........至于随机抽取：
先做查询，给每条记录加一个随机数，然后再根据随机数排序，利用
select top 跟 where 配合来取刚刚计算得出的记录数，然后计算“权重”之和，权重不符的，可以制定一个逻辑来调整，
比如符合条件的记录根据权重排序，跟据权重偏大、或偏小不断取向近的记录来调整，直到精确匹配。最终把各个记录集用union合并起来。精确为150

Answer 5

唉、、、经常有人叫我兄的，其实叫兄还算稍微好点，还有人发短消息叫我“哥哥”的，我差点没晕倒~~~其实上面的语句少写了一点：
select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt1 as varchar)+' t1.f1,t1.f2,t1.f3,t1.f4 '+
。。改成：
select @sql='insert into #temp select * from '+
'(select top '+cast(@cnt1 as varchar)+' t1.id t1.f1,t1.f2,t1.f3,t1.f4 '+
。。因为最后的结果集要把ID带出来。 fuxc(Michael)的方法我也想过，但不好做，因为贴主要求记录数及权重和是精确的，分布只要大约就行了。用这个方法不好去找记录的，找完记录，再去调节以满足条件，最后的结果还是满足不了分布的要求的。

Answer 6

既然大家都认为icevi(按钮工厂)是JJ，同时为防icevi(按钮工厂)再次晕倒，我也只好勉为其难，叫icevi(按钮工厂)JJ了。你的上段语句已拜读，的确比较有意思，但是还没达到理想状态。
表tProducts数据大约有50万条，满足条件的精确解组合都极可能有成千上万组，最好从这成千上万组组中随机挑出一组。不能满足才退而求其次，且其精度也应尽可能大。
没想到这个问题如此之难，为大家阵亡的脑细胞致敬！

Answer 7

yokel(网络幽魂) ：等我回家再想想，好在我做的随机数据都还在：）hillhx(曾经的曾经)：当然上班了，不上班难道饿死哦~ ，没看到我上面发的贴子都是晚上吗？我白天上班上来得少，偶尔趁收发邮件上来瞅一眼而已。 大部分贴子都是晚上才回的哦~~贴主这个东东可能与数据挖掘没什么关系吧，只是做分布统计而已。

Answer 8

--******先准备数据**************
--生成CSDN及stand1~3这几个表，语句上面有，不再重复了
--再构造四个表，分别存放满足分面的组合，每个表都只有40条记录
create table temp1 (f1 char(1))
create table temp2 (f2 char(1))
create table temp3 (f3 char(1))
create table temp4 (f4 char(1))
--往每个表里插入40条记录，因语句太多，
--我是用个简单办法产生的语句，没有用SQL来产生，所以就不写出来了。
insert into temp1 values (1)  --需重复生成8条，以下类似
--车间
--值    比例    需生成的记录数
-- 1 0.2 8
-- 2 0.1 4
-- 3 0.2 8
-- 4 0.3 12
-- 5 0.2 8insert into temp2 values ('A')
--类型 
--值    比例    需生成的记录数
-- A 0.3 12
-- B 0.2 8
-- C 0.4 16
-- D 0.1 4
insert into temp3 values (1)
--要求
--值    比例    需生成的记录数
-- 1 0.1 4
-- 2 0.3 12
-- 3 0.2 8
-- 4 0.4 16--**权重对应的这个表里的记录，每次不同，但需要总和为40，加权和为150**
insert into temp4 values (1)
--权重 
--值    需生成的记录数
-- 1 0
-- 2 6
-- 3 9
-- 4 14
-- 5 11--******下面开始生成结果集了**********
--分别生成随机排列
select identity(int,1,1) id ,f1 into #temp1 
from (select top 100 percent f1 from temp1 order by newid() )t1select identity(int,1,1) id ,f2 into #temp2 
from (select top 100 percent f2 from temp2 order by newid() )t1select identity(int,1,1) id ,f3 into #temp3 
from (select top 100 percent f3 from temp3 order by newid() )t1select identity(int,1,1) id ,f4 into #temp4 
from (select top 100 percent f4 from temp4 order by newid() )t1--生成随机组合
select f1,f2,f3,f4 into #ready
from #temp1 join #temp2 on #temp1.id=#temp2.id join #temp3 on #temp1.id=#temp3.id
join #temp4 on #temp1.id=#temp4.id --按#ready的记录到csdn表里去找一条随机记录做为入选记录
select top 0 id,f1,f2,f3,f4 into #temp from csdnDECLARE @f1 char(1), @f2 char(1), @f3 char(1), @f4 char(1)DECLARE mycursor CURSOR FOR SELECT f1,f2,f3,f4 FROM #readyOPEN mycursorFETCH NEXT FROM mycursor INTO   @f1 , @f2 , @f3 , @f4 WHILE @@FETCH_STATUS = 0
BEGIN
   insert into #temp select top 1 * from csdn 
     where csdn.f1=@f1 and csdn.f2=@f2 and csdn.f3=@f3 and csdn.f4=@f4
     order by newid()
   FETCH NEXT FROM mycursor INTO @f1 , @f2 , @f3 , @f4 
ENDCLOSE mycursor
DEALLOCATE mycursor
GO--******得到我们要的结果啦！*******
select * from #temp order by id--验证
select count(*) as cnt,sum(cast(f4 as int)) as sum from #tempselect #temp.f1,count(*)/.4 as rate,stand1.stand 
from #temp join stand1 on #temp.f1=stand1.f1 
group by #temp.f1,stand1.stand order by #temp.f1select #temp.f2,count(*)/.4 as rate,stand2.stand 
from #temp join stand2 on #temp.f2=stand2.f1 
group by #temp.f2,stand2.stand order by #temp.f2select #temp.f3,count(*)/.4 as rate,stand3.stand 
from #temp join stand3 on #temp.f3=stand3.f1 
group by #temp.f3,stand3.stand order by #temp.f3--删除临时表
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
drop table #ready
drop table #temp

Answer 9

得到的结果之一：
id          f1   f2   f3   f4   
----------- ---- ---- ---- ---- 
334         4    A    4    2
1530        3    B    4    5
2073        3    C    1    4
5295        5    D    2    5
5309        2    C    2    4
5587        2    A    3    5
5952        3    A    4    4
7742        4    A    4    2
10285       1    C    4    4
10875       5    A    1    5
11637       1    C    3    3
11705       4    D    4    4
12625       3    A    3    3
14892       5    C    2    3
14927       3    C    2    5
14987       1    C    4    4
14997       4    B    4    3
17493       4    B    4    3
17732       4    C    2    4
18296       5    C    3    4
19498       5    C    1    2
19593       4    C    2    4
19630       4    A    3    2
20717       1    B    4    4
21350       3    C    4    4
21542       3    C    4    2
22579       4    B    4    3
23915       1    C    2    5
24461       5    C    1    5
24557       4    B    2    4
24671       1    A    2    5
24770       3    B    4    4
25291       2    A    3    3
25547       1    A    3    2
26240       2    A    2    5
26638       4    A    2    3
27096       1    B    4    4
28051       4    C    2    5
28822       5    D    3    3
29855       5    D    4    5cnt         sum         
----------- ----------- 
40          150f1   rate                stand       
---- ------------------- ----------- 
1    20.000000           20
2    10.000000           10
3    20.000000           20
4    30.000000           30
5    20.000000           20f2   rate                stand       
---- ------------------- ----------- 
A    30.000000           30
B    20.000000           20
C    40.000000           40
D    10.000000           10f3   rate                stand       
---- ------------------- ----------- 
1    10.000000           10
2    30.000000           30
3    20.000000           20
4    40.000000           40

Answer 10

OpenVMS(半知半解) ：那些临时表大多与原表没关系，为什么不试试再说？如果你有好的办法可以写出来大家共享一下。

Answer 11

To:hillhx(曾经的曾经) 
   我也觉得与数据挖掘没太大关系。有人对我说，如果能把如题这种复杂的查询搞定，就可以跳槽了，看来我要潦倒一生了。可实在不服气，越想越气，而这个问题又绝对经典，才交给大家讨论。To: icevi(按钮工厂) （JJ||MM）
   十分感谢，我晚上再试试,我就不信如此邪门。To:OpenVMS(半知半解)
   小条件（满足可能性小的）放在前面挑选先挑选，再逐步做?小弟不解，怎么实现，速度会快吗？To: All
    希望大家积极讨论，最终能有个完善高效的解决方法。

Answer 12

我试过了， icevi(按钮工厂)的方法基本可行，尽管下段代码不是很令人满意
--**权重对应的这个表里的记录，每次不同，但需要总和为40，加权和为150**
insert into temp4 values (1)
--权重 
--值    需生成的记录数
-- 1 0
-- 2 6
-- 3 9
-- 4 14
-- 5 11
性能有如OpenVMS(半知半解)所言不是很好，我的爱机p3 866,ram 192m,费时23秒左右。渴望高招
我准备本周五结帖，令开200分寻高招，各位，继续努力啊

Answer 13

一、权重的问题我觉得只有这样解决，可以象我前面说的，先用穷举法算出各种可能，存入一个表中，每次取一条随机记录就可以了。二、性能问题，我那段代码最耗时间的就只有这一条语句：   insert into #temp select top 1 * from csdn 
     where csdn.f1=@f1 and csdn.f2=@f2 and csdn.f3=@f3 and csdn.f4=@f4
     order by newid()如果对这几个字段加上索引的话，速度会非常快。我用我的几万条记录试就已经很明显了。而且我觉得这样处理每次运行的速度都是差不多的，比较均衡，如果是没有目的地到表里取随机的记录，再看是否合条件，再调整，可能运算量不确定，两次运算速度可能会相差很大。与你一起期待更好的解决方法。三、很好奇，你做这样的查询有什么实际作用吗？能否解释一下？我对于分布统计方面的东西比较感兴趣，如果能让我长点知识我会很高兴。不方便写出来可以发短消息给我，或是EMAIL给我：[email protected]

Answer 14

TO：icevi(按钮工厂），你真是厉害，佩服！！！

Answer 15

To: icevi(按钮工厂) 
    有道理。至于这样的查询有什么实际作用，事关单位机密，原谅我不能言明，只能告诉你这样的查询可用于多角度筛选（好象是句废话），在特定专业有着某一方面有着重要应用。
    我会另开200分给你。感谢各位的参与。

Answer 16

按钮工厂提供的方法巨好啊！特别是权重问题只能采用你的方案别无它法。
但具体代码有几个地方可以改进。1、cursor循环中样本数据可能被重复选中
2、一次cursor循环中可能没有符合＃ready条件的记录，导致＃temp中数据不够40条。
在--******下面开始生成结果集了**********后面可以作可能的改进如下：
declare @w1,@w2,@w3,@w4,@w5,@w1_cnt,@w2_cnt,@w3_cnt,@w4_cnt,@w5_cnt;
select @w1=1,@w2=2,@w3=3,@w4=4,@w5=5,@w1_cnt=0,@w2_cnt=6,@w3_cnt=9,@w4_cnt=14,@w5_cnt=11;declare @rand_f1,@rand_f2,@rand_f3 char(1);
declare @i int;--选出@w1_cnt个权重为@w1的记录。(可以单独编成过程)。
set @i=0 
while i<@w1_cnt do
  begin
  select top 1 @rand_f1=f1 from temp1 order by newid;
  select top 1 @rand_f2=f2 from temp2 order by newid;
  select top 1 @rand_f3=f3 from temp3 order by newid;
  insert into #temp 
    select top 1 id ,f1,f2,f3,f4 from csdn 
         where f4=@w1 --确保权重和为150
         and f1 =rand_f1 and f2=rand_f2 and f3=rand_f3 --确保各字段的概率分布
         and not exists (select id from #temp where #temp.id=csdn.id)--确保不会重复选中记录。
         order by NewID --确保随机性 : 方法很土，效率低，有较好方法吗？
  if @@rowcount > 0 then
set i=i+1;
  end;
--同样依次选出@wn_cnt个权重为@wn的记录。n=(2,5)
略。
--得到结果数据
select * from #temp;
还有几个疑问：
1：当f1,f2,f3,w(F4是也)互相独立时，单项f1,f2,f3组合后概率分布与选择时的概率分布与相同吗？(能够理论证明吗？)
2：效率问题：从50万条记录中选符合条件的一条随机记录用select top 1 ** order by newid是不是很落后？