22,302
社区成员




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER 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
begin
set @end=charindex(',',@par,@start)
print substring(@par,@start,@end-@start);
set @start=@end +1
if @end=len(@par)
set @flag=1
set @i=@i+1
end
SET NOCOUNT ON;
END
create table AllElectronics(TID varchar(10),[List of item_ID's] varchar(500))
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 行受影响)
*/
这样麻烦点,但兼容性强create table AllElectronics(TID varchar(10),[List of item_ID's] varchar(500))
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 ,
项集 b
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 行受影响)
*/
这样好点create table AllElectronics(TID varchar(10),[List of item_ID's] varchar(500))
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 行受影响)