create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 5 , 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
union all select 6, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页,aspx是动态网页PHP、PHP'
union all select 7, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
go
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where kw.word in ('asp','aspx','php')
group by id,content,kw.word
),
tt
as
(
select --max(次数) over(PARTITION by id) as max_count,
min(次数) over(partition by id) as min_count,
id,content,word,次数
from t
)
select id,content
from
(
select *,
--asp的出现次数为6次
ROW_NUMBER() over(order by abs(次数 - 6 )) as rownum
from tt
where min_count > 0 --次数最少的都大于0,说明所有关键字的次数都大于0
)t
where rownum = 1
and word = 'asp'
/*
id content
5 ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页
*/
[/quote]
sql2000 的写啊? [/quote]
改成适合2000的了,用了临时表:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 5 , 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
union all select 6, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页,aspx是动态网页PHP、PHP'
union all select 7, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
go
If OBJECT_ID('tempdb..#t') is not null
drop table #t
If OBJECT_ID('tempdb..#tt') is not null
drop table #tt
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
into #t
from tb
inner join key_word kw
on 1=1
where kw.word in ('asp','aspx','php')
group by id,content,kw.word
select t.id,content,word,次数,min_count
into #tt
from #t t
inner join
(
select id,min(次数) min_count
from #t
group by id
)tt
on t.ID = tt.ID
select top 1 *
from #tt
where min_count > 0 --次数最少的都大于0,说明所有关键字的次数都大于0
and word = 'asp'
order by abs(次数 - 6 )
/*
id content
5 ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页
*/
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 5 , 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
union all select 6, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页,aspx是动态网页PHP、PHP'
union all select 7, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
go
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where kw.word in ('asp','aspx','php')
group by id,content,kw.word
),
tt
as
(
select --max(次数) over(PARTITION by id) as max_count,
min(次数) over(partition by id) as min_count,
id,content,word,次数
from t
)
select id,content
from
(
select *,
--asp的出现次数为6次
ROW_NUMBER() over(order by abs(次数 - 6 )) as rownum
from tt
where min_count > 0 --次数最少的都大于0,说明所有关键字的次数都大于0
)t
where rownum = 1
and word = 'asp'
/*
id content
5 ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页
*/
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 5 , 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
union all select 6, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页,aspx是动态网页PHP、PHP'
union all select 7, 'ASP,PHP用来做网页都不错ASP,做网页都不错ASP,aspx是动态网页'
go
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where kw.word in ('asp','aspx','php')
group by id,content,kw.word
),
tt
as
(
select --max(次数) over(PARTITION by id) as max_count,
min(次数) over(partition by id) as min_count,
id,content,word,次数
from t
)
select id,content
from
(
select *,
--asp的出现次数为6次
ROW_NUMBER() over(order by abs(次数 - 6 )) as rownum
from tt
where min_count > 0 --次数最少的都大于0,说明所有关键字的次数都大于0
)t
where rownum = 1
and word = 'asp'
/*
id content
5 ASP,PHP用来做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,做网页都不错ASP,aspx是动态网页
*/
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id <> 5
group by id,content,kw.word
),
tt
as
(
select top 1 id
from t
where (word = 'asp' and 次数<=4) or
(word = 'aspx' and 次数<=6)
order by 次数 desc
)
select *
from tb
where id in (select id from tt)
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id <> 5
group by id,content,kw.word
),
tt
as
(
select top 1 id
from t
where (word = 'asp' and 次数<=4) or
(word = 'aspx' and 次数<=6)
order by 次数 desc
)
select *
from tb
where id in (select id from tt)
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id <> 5
group by id,content,kw.word
),
tt
as
(
select top 1 id
from t
where (word = 'asp' and 次数<=4) or
(word = 'aspx' and 次数<=6)
order by 次数 desc
)
select *
from tb
where id in (select id from tt)
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id <> 5
group by id,content,kw.word
),
tt
as
(
select top 1 id
from t
where (word = 'asp' and 次数<=4) or
(word = 'aspx' and 次数<=6)
order by 次数 desc
)
select *
from tb
where id in (select id from tt)
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
;with t
as
(
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id <> 5
group by id,content,kw.word
),
tt
as
(
select top 1 id
from t
where (word = 'asp' and 次数<=4) or
(word = 'aspx' and 次数<=6)
order by 次数 desc
)
select *
from tb
where id in (select id from tt)
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/
--1.函数
if exists(select * from sys.objects where name = 'f_searchSTR' and type = 'tf')
drop function dbo.f_searchSTR
go
create function dbo.f_searchSTR
(
@s varchar(max), --要分拆的字符串
@search varchar(30) --分隔字符
)
returns int
as
begin
declare @len int
declare @i int;
set @i = 0;
while CHARINDEX(@search,@s) >0
begin
set @i = @i + 1
set @s = stuff(@s,CHARINDEX(@search,@s),len(@search),'')
end
return @i
end
go
然后是查询:
create table key_word(word nvarchar(30))
insert into key_word
select 'JAVA' union all
select 'PHP' union all
select 'asp' union all
select 'aspx' union all
select 'FLEX'
go
drop table tb
go
create table tb(id int,content nvarchar(max))
insert into tb
select 1,'JAVA是面对对象的语言,java阿斯蒂芬到发斯蒂芬撒旦php了空间和空间空间php了空间和空间空间'
union all select 2,'PHP学起来比JAVA要容易点asp了互动粉卡萨帝合肥PHP了看见了看见了健康路家'
union all select 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单'
union all select 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用'
union all select 5,'ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页'
go
select id,
content,
kw.word,
dbo.f_searchSTR(tb.content,kw.word) 次数
from tb
cross join key_word kw
where id = 5
group by id,content,kw.word
/*
id content word 次数
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 asp 3
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 aspx 1
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 FLEX 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 JAVA 0
5 ASP,PHP用来做网页都不错ASP,PHP都是动态网页阿斯顿发房间撒地方PHP都是动态网页aspx也是动态网页 PHP 3
*/