try: select count(id) from tb where charindex('a',a)>0 and charindex('b',b)>0
再簡化一下. select count(1) from tb where charindex('a',a)>0 and charindex('b',b)>0
在a或者b上建立索引 like‘'%a%' 这样的用不到索引 like 'a%' 这样的可以
Create table IndexTest (ID int identity primary key, A nvarchar(50), B nvarchar(50))declare @i int,@j intselect @i=0, @j=0while @i<1255 begin set @j=0 while @j<2550 begin insert into indextest(a,b) select char(@i),char(@j) set @j=@j+1 endset @i=@i+1 end--drop index index_a_b on IndexTestcreate index index_a_b on IndexTest(a,b) select count(*) from IndexTest with (nolock) where a like 'a%' and b like 'b%' ----------------------- --200多万条3秒钟,我的机器配置并不高,注意索引必须同时建在a,b列上
select count(id) from tb where charindex('a',a)>0 and charindex('b',b)>0
再簡化一下.
select count(1) from tb where charindex('a',a)>0 and charindex('b',b)>0
在a或者b上建立索引
like‘'%a%' 这样的用不到索引
like 'a%' 这样的可以
(ID int identity primary key,
A nvarchar(50),
B nvarchar(50))declare @i int,@j intselect @i=0,
@j=0while @i<1255
begin
set @j=0
while @j<2550
begin
insert into indextest(a,b)
select char(@i),char(@j) set @j=@j+1
endset @i=@i+1
end--drop index index_a_b on IndexTestcreate index index_a_b on IndexTest(a,b)
select count(*) from IndexTest with (nolock)
where a like 'a%' and b like 'b%'
-----------------------
--200多万条3秒钟,我的机器配置并不高,注意索引必须同时建在a,b列上