34,588
社区成员
发帖
与我相关
我的任务
分享
--建表:
if object_id('Poesy','u') is not null
drop table Poesy
go
create table Poesy
(
id int identity(1,1),
Title varchar(100),
Tag varchar(100)
)
go
alter table Poesy add constraint pk_Posy primary key(Id)
go
--造数据
declare @Number int,@NumberTop int,@TagId int
Set @TagId = 1
Set @number = 1
Set @numberTop = 10000000
while @number<=@numberTop
begin
if @TagId = 100
Set @TagId = 1
INSERT Poesy(Title,Tag)
select 'title'+cast(@number as varchar), '历史'++cast(@TagId as varchar)+',铁木真,元朝历史,蒙古历史'+cast(@number as varchar)
Set @number = @number +1
Set @TagId = @TagId +1
end
--建立索引
create index Ix_poesyTag on Poesy(Tag)
--查询 很慢要一分钟以上(1)
with Orderlist as (
select row_number() over(order by id desc) as rownumber,id,title
from poesy
where charindex(',蒙古历史10000,',','+tag+',')>0
) select RowNumber,Id,Title
from Orderlist
where RowNumber between 1 and 50
--查询很快(2)
with Orderlist as (
select row_number() over(order by id desc) as rownumber,id,title
from poesy
where charindex(',元朝历史,',','+tag+',')>0
) select RowNumber,Id,Title
from Orderlist
where RowNumber between 1 and 50
tag like '%蒙古历史10000%'