1种方式,sql处理
写一个function
create function dbo.fn_test(@xj nvarchar(5))
returns int
as
begin
declare @a nvarchar(02),@b nvarchar(02)
select @a=left(@xj,2),@b=right(@xj,2)
declare @reture
select @reture=count(*) from Table_Name
where charindex(','+@a+',' , ','+rtrim(ListOfItems)+',')>0
and charindex(','+@b+',' , ','+rtrim(ListOfItems)+',')>0return isnull(@return,0)
endGoselect dbo.fn_test(项集) as cnt from table_项
写一个function
create function dbo.fn_test(@xj nvarchar(5))
returns int
as
begin
declare @a nvarchar(02),@b nvarchar(02)
select @a=left(@xj,2),@b=right(@xj,2)
declare @reture
select @reture=count(*) from Table_Name
where charindex(','+@a+',' , ','+rtrim(ListOfItems)+',')>0
and charindex(','+@b+',' , ','+rtrim(ListOfItems)+',')>0return isnull(@return,0)
endGoselect dbo.fn_test(项集) as cnt from table_项
定义
I1 : 2
I2 : 3
I3 : 5
I4 : 7
I5 : 11
...(素数)不管哪几个数组合,其乘机是唯一的。比如 I1,I3,I4 =2*5*7=70
所以,在项中,任意2个组合 I1,I3 和I1,I4和I3,I4 其积10,14,35都是可以被70整除的(也是素数两两组合唯一的)
只要求 两两乘积哪些可以被整除就可以统计了
insert AllElectronics select 'T100','I1,I2,I5'
insert AllElectronics select 'T200','I2,I4'
insert AllElectronics select 'T300','I2,I3'
insert AllElectronics select 'T400','I1,I2,I4'
insert AllElectronics select 'T500','I1,I3'
insert AllElectronics select 'T600','I2,I3'
insert AllElectronics select 'T700','I1,I3'
insert AllElectronics select 'T800','I1,I2,I3,I5'
insert AllElectronics select 'T900','I1,I2,I3'
go
create table 项集(项集 varchar(10))
insert 项集 select '{I1,I2}'
insert 项集 select '{I1,I3}'
insert 项集 select '{I1,I4}'
insert 项集 select '{I1,I5}'
insert 项集 select '{I2,I3}'
insert 项集 select '{I2,I4}'
insert 项集 select '{I2,I5}'
insert 项集 select '{I3,I4}'
insert 项集 select '{I3,I5}'
insert 项集 select '{I4,I5}'
select
项集,
支持度计数=sum(case when charindex(left(replace(replace(项集,'{',','),'}',','),3),','+[List of item_ID's]+',')>0
and
charindex(right(replace(replace(项集,'{',','),'}',','),3),','+[List of item_ID's]+',')>0
then 1
else 0
end)
from AllElectronics a ,
项集 b
--where charindex(replace(replace(项集,'{',','),'}',','),','+[List of item_ID's]+',')>0
group by 项集
drop table AllElectronics,项集
项集 支持度计数
---------- -----------
{I1,I2} 4
{I1,I3} 4
{I1,I4} 1
{I1,I5} 2
{I2,I3} 4
{I2,I4} 2
{I2,I5} 2
{I3,I4} 0
{I3,I5} 1
{I4,I5} 0(10 行受影响)
insert AllElectronics select 'T100','I1,I2,I5'
insert AllElectronics select 'T200','I2,I4'
insert AllElectronics select 'T300','I2,I3'
insert AllElectronics select 'T400','I1,I2,I4'
insert AllElectronics select 'T500','I1,I3'
insert AllElectronics select 'T600','I2,I3'
insert AllElectronics select 'T700','I1,I3'
insert AllElectronics select 'T800','I1,I2,I3,I5'
insert AllElectronics select 'T900','I1,I2,I3'
go
create table 项集(项集 varchar(10))
insert 项集 select '{I1,I2}'
insert 项集 select '{I1,I3}'
insert 项集 select '{I1,I4}'
insert 项集 select '{I1,I5}'
insert 项集 select '{I2,I3}'
insert 项集 select '{I2,I4}'
insert 项集 select '{I2,I5}'
insert 项集 select '{I3,I4}'
insert 项集 select '{I3,I5}'
insert 项集 select '{I4,I5}'
select
项集,
支持度计数=sum(case when charindex(left(replace(replace(项集,'{',''),'}',''),2),[List of item_ID's])>0
and
charindex(right(replace(replace(项集,'{',''),'}',''),2),[List of item_ID's])>0
then 1
else 0
end)
from AllElectronics a ,
项集 bgroup by 项集
/*
drop table AllElectronics,项集项集 支持度计数
---------- -----------
{I1,I2} 4
{I1,I3} 4
{I1,I4} 1
{I1,I5} 2
{I2,I3} 4
{I2,I4} 2
{I2,I5} 2
{I3,I4} 0
{I3,I5} 1
{I4,I5} 0(10 行受影响)
*/这样好点
insert AllElectronics select 'T100','I1,I2,I5'
insert AllElectronics select 'T200','I2,I4'
insert AllElectronics select 'T300','I2,I3'
insert AllElectronics select 'T400','I1,I2,I4'
insert AllElectronics select 'T500','I1,I3'
insert AllElectronics select 'T600','II2,I4'
insert AllElectronics select 'T700','I1,I3'
insert AllElectronics select 'T800','I1,I22,I3,I5'
insert AllElectronics select 'T900','I1,I2,I3'
go
create table 项集(项集 varchar(10))
insert 项集 select '{I1,I2}'
insert 项集 select '{I1,I3}'
insert 项集 select '{I1,I4}'
insert 项集 select '{I1,I5}'
insert 项集 select '{I2,I3}'
insert 项集 select '{II2,I4}'
insert 项集 select '{I22,I5}'
insert 项集 select '{I3,I4}'
insert 项集 select '{I3,I5}'
insert 项集 select '{I4,I5}'select
项集,
支持度计数=sum(case when charindex(substring(项集,2,charindex(',',项集)-2),[List of item_ID's])>0
and
charindex(substring(项集,charindex(',',项集)+1,len(项集)-1-charindex(',',项集)),[List of item_ID's])>0
then 1
else 0
end)
from AllElectronics a ,
项集 b
group by 项集
drop table AllElectronics,项集/*
项集 支持度计数
---------- -----------
{I1,I2} 4
{I1,I3} 4
{I1,I4} 1
{I1,I5} 2
{I2,I3} 3
{I22,I5} 1
{I3,I4} 0
{I3,I5} 1
{I4,I5} 0
{II2,I4} 1(10 行受影响)
*/这样麻烦点,但兼容性强
那你是怎么输出结果的?我想吧计算结果输出到程序里面
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[Par]
-- Add the parameters for the stored procedure here
@par varchar(1000)='enlish,chinese,math,political,physics,'
AS
BEGIN
declare @flag int,@start int,@end int,@i int;
set @flag=0;
set @start=0;
set @i=1;
while @flag=0
beginset @end=charindex(',',@par,@start)print substring(@par,@start,@end-@start);set @start=@end +1
if @end=len(@par)
set @flag=1set @i=@i+1end
SET NOCOUNT ON;
END这个是我写的截取字符串的过程