case when (A.f1=SUBSTRING(b.f1,1,1) or A.f1=SUBSTRING(b.f1,2,1) or A.f1=SUBSTRING(b.f1,3,1) ) then 1 else 0 end这个估计快点, 只要A.f1包含于b.f1中 就...case when patIndex('%'+A.f1+'%',b.f1)>0 then 1 else 0 end
declare @A table(Name varchar(50)) insert into @A select '赵' insert into @A select '钱' insert into @A select '孙' insert into @A select '李' insert into @A select '周' insert into @A select '吴' insert into @A select '郑' insert into @A select '王' declare @B table(Name varchar(50)) insert into @B select '赵本山' insert into @B select '钱学森' insert into @B select '孙继海' insert into @B select '李全' select case when (CHARINDEX(A.Name, B.Name)>0) then 1 else 0 end,A.Name,B.Name from @A A,@B B
明明CHARINDEX更直观,效率也高一点:case when CHARINDEX(A.f1,b.f1)>0 then 1 else 0 end
我运行结果是原来的快一点,其次是case when CHARINDEX(A.f1,b.f1)>0 then 1 else 0 end,case when patIndex('%'+A.f1+'%',b.f1)>0 then 1 else 0 end又慢一点
不知道你怎么测的!差距很明显!(512 行受影响)SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。(512 行受影响)SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 100 毫秒。
insert into @A select '赵'
insert into @A select '钱'
insert into @A select '孙'
insert into @A select '李'
insert into @A select '周'
insert into @A select '吴'
insert into @A select '郑'
insert into @A select '王'
declare @B table(Name varchar(50))
insert into @B select '赵本山'
insert into @B select '钱学森'
insert into @B select '孙继海'
insert into @B select '李全'
select case when (CHARINDEX(A.Name, B.Name)>0) then 1 else 0 end,A.Name,B.Name from @A A,@B B
明明CHARINDEX更直观,效率也高一点:case when CHARINDEX(A.f1,b.f1)>0 then 1 else 0 end
CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。(512 行受影响)SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 100 毫秒。