统计一些项目的组合 3

Question

--偷懒一把，先借用邹建的存储过程，在p_qry中调用
create proc p_qry_1
@count int=1 --组合的项数
as
declare @sa Nvarchar(4000),@sb Nvarchar(4000)
declare @s2 Nvarchar(4000),@s3 Nvarchar(4000)
declare @s varchar(8000)if isnull(@count,0)<0 set @count=1
select a=name,b=colid
into #t from syscolumns 
where id=object_id(N't1') and name<>'id'
set @count=case when @count>@@rowcount then @@rowcount else @count endif @count=1
set @sa='select @s=@s+'' union all select item=[''+a+''],[count]=count(*) from t1 group by [''+a+'']'' from #t'
else
begin
select @sa='select @s=@s+'' union all select item=[''+a.a+'']'''
,@sb='''[''+a.a+'']'''
,@s2='from #t a'
,@s3='where a.b'
while @count>1
select @count=@count-1
,@sa=@sa+'+''+''''_''''+[''+'+'_t'+cast(@count as varchar(5))+'.a+'']'''
,@sb=@sb+'+'',[''+'+'_t'+cast(@count as varchar(5))+'.a+'']'''
,@s2=@s2+',#t '+'_t'+cast(@count as varchar(5))
,@s3=@s3+'<'+'_t'+cast(@count as varchar(5))+'.b'
+' and '+'_t'+cast(@count as varchar(5))+'.b'
select @sa=@sa+'+'',[count]=count(*) from t1 group by ''+'+@sb+' '+@s2+' '+left(@s3,len(@s3)-9)
endset @s=''
exec sp_executesql @sa,N'@s varchar(8000) out',@s out
set @s=stuff(@s,1,11,'insert into #rt ')exec(@s)
go--存储过程:计算相关度
create proc p_qry
@count int
as
declare @c int,@spchar varchar(1000),@intc int
create table #rt(item varchar(1000),count int)set @c=1
while @c<=@count
begin
exec p_qry_1 @c
set @c=@c+1
endselect item=a.item+':'+b.item,corr=cast(e.count as varchar(10))+'/'+cast(a.count as varchar(10))
from (
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) a
cross join
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) b
inner join
(select * from #rt where len(item)-len(replace(item,'_',''))=@count-1) e
on a.item+'_'+b.item=e.item) drop table #rt
go--构造测试数据
create table t1(id int,Place char(2),Time char(2),Level char(2))
insert into t1 select 1 ,'P1','T3','L1'
union select 2 ,'P1','T1','L1'
union select 3 ,'P3','T1','L2'
union select 4 ,'P2','T2','L3'
union select 5 ,'P1','T1','L1'
union select 6 ,'P2','T3','L3'--测试
exec p_qry 3--删除对象
drop table t1
drop proc p_qry_1,p_qry--返回结果
item                  corr                  
--------------------- --------------------- 
P1:T1_L1              2/3
P1:T3_L1              1/3
P2:T2_L3              1/2
P2:T3_L3              1/2
P3:T1_L2              1/1
P1_T1:L1              2/2
P3_T1:L2              1/1
P2_T2:L3              1/1
P1_T3:L1              1/1
P2_T3:L3              1/1--说明:如果P1_T1_L1中某子集P1_T1的count为0，则未做该子集的统计。你的非空子集是否此意？

Answer 1

补充：declare @c int,@spchar varchar(1000),@intc int这句中的,@spchar varchar(1000),@intc int2个变量的测试时用的，忘了删了，可以删除.

Answer 2

上面存储过程p_qry有误，更正如下：create proc p_qry
@count int
as
declare @c int
create table #rt(item varchar(1000),count int)set @c=1
while @c<=@count
begin
exec p_qry_1 @c
set @c=@c+1
endselect item=a.item+':'+b.item,corr=cast(e.count as varchar(10))+'/'+cast(a.count as varchar(10))
from (
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) a
cross join
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) b
inner join
(select * from #rt where len(item)-len(replace(item,'_',''))=@count-1) e
on len(a.item+'_'+b.item)-len(replace(a.item+'_'+b.item,'_',''))=@count-1
and (replace(e.item+'_',a.item+'_','')=b.item+'_' or replace(e.item+'_',b.item+'_','')=a.item+'_'))       --这里的条件做了修正drop table #rt

Answer 3

谢谢 huwgao(小楼听雨) ！ 
“如果P1_T1_L1中某子集P1_T1的count为0，则未做该子集的统计。你的非空子集是否此意？” ----在已经统计出来的2项集和3项集中，它们各自所有的非空子集的Count都是不为0的！ 因为t1中必然存在同时包含"P1"、"T1、"L1"的纪录，因此对于"P1_T1_L1"的任何非空子集，也必然在t1中存在纪录包含它们。
你给出的存储过程我还没来的及仔细看，如果结果验证完全正确，那就太感谢了！ ^_^

Answer 4

to huwgao(小楼听雨):
请问 p_qry_1 中的"set @s=stuff(@s,1,11,'insert into #rt ')" 是不是应该是"set @s=stuff(@s,1,11,'')" 啊？  我单独执行 p_qry_1 的时候，总会在这一句报错，但是 p_qry能够顺利执行

Answer 5

不能单独执行  p_qry_1
因为它只是被 p_qry 调用,协助 p_qry 做处理的. #rt 这个临时表来源于 p_qry如果是要单独调用 p_qry_1,则如楼主所说,改为 set @s=stuff(@s,1,11,'')
但改了后,就不能为 p_qry 提供正确的数据了

Answer 6

--重新整理了，整合在一个存储过程中create proc p_qry_2
@count int=2
as
declare @sa Nvarchar(4000),@sb Nvarchar(4000)
declare @s2 Nvarchar(4000),@s3 Nvarchar(4000)
declare @s varchar(8000)--获取t1表要统计项目字段信息，插入临时表#t
if isnull(@count,0)<0 set @count=2
select a=name,b=colid
into #t from syscolumns 
where id=object_id(N't1') and name<>'id'
set @count=case when @count>@@rowcount then @@rowcount else @count enddeclare @c int,@cc int
create table #rt(item varchar(1000),count int)--统计从1项到@count项的所有组合，插入临时表#rt
select @c=1
while @c<=@count
begin
if @c=1
set @sa='select @s=@s+'' union all select item=[''+a+''],[count]=count(*) from t1 group by [''+a+'']'' from #t'
else
begin
select @sa='select @s=@s+'' union all select item=[''+a.a+'']'''
,@sb='''[''+a.a+'']'''
,@s2='from #t a'
,@s3='where a.b'
,@cc=1
while @cc<@c
begin
select @cc=@cc+1
,@sa=@sa+'+''+''''_''''+[''+'+'_t'+cast(@cc as varchar(5))+'.a+'']'''
,@sb=@sb+'+'',[''+'+'_t'+cast(@cc as varchar(5))+'.a+'']'''
,@s2=@s2+',#t '+'_t'+cast(@cc as varchar(5))
,@s3=@s3+'<'+'_t'+cast(@cc as varchar(5))+'.b'
+' and '+'_t'+cast(@cc as varchar(5))+'.b'
end
select @sa=@sa+'+'',[count]=count(*) from t1 group by ''+'+@sb+' '+@s2+' '+left(@s3,len(@s3)-9)
end

set @s=''
exec sp_executesql @sa,N'@s varchar(8000) out',@s out
set @s=stuff(@s,1,11,'insert into #rt ')
exec(@s) set @c=@c+1
end--计算临时表#rt中@count项的相关度
select item=a.item+':'+b.item,corr=cast(e.count as varchar(10))+'/'+cast(a.count as varchar(10))
from (
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) a
cross join
(select * from #rt where len(item)-len(replace(item,'_',''))<@count-1) b
inner join
(select * from #rt where len(item)-len(replace(item,'_',''))=@count-1) e
on len(a.item+'_'+b.item)-len(replace(a.item+'_'+b.item,'_',''))=@count-1
and (replace(e.item+'_',a.item+'_','')=b.item+'_' or replace(e.item+'_',b.item+'_','')=a.item+'_'))drop table #rt
go--构造测试数据
create table t1(id int,Place char(2),Time char(2),Level char(2),MM char(2))
insert into t1 select 1 ,'P1','T3','L1','M1'
union select 2 ,'P1','T1','L1','M1'
union select 3 ,'P3','T1','L2','M2'
union select 4 ,'P2','T2','L3','M3'
union select 5 ,'P1','T1','L1','M2'
union select 6 ,'P2','T3','L3','M1'--测试
exec p_qry_2 3--删除对象
drop table t1
drop proc p_qry_2

Answer 7

谢谢二位！ 结果完全正确！！
我继续另开贴加分，题目是"统计一些项目的组合 4 "
望 邹建和 huwgao(小楼听雨) 二位大哥赏脸！