我现有一张表,结构如下，我想输入几个词，如何统计出这几个词在每条记录中出现的个数，并按个数进行倒序排序，
表的内容：
I大炮 Keys
1 汽车%轮船%轮船%大炮%坦克%火箭
2 轮船%大炮%坦克%火箭
3 大炮%坦克%火箭
4 轮船%轮船%大炮%坦克
5 坦克
比如，我输入汽车,轮船,轮船,大炮,坦克 五个词进行检索，全出现的排第一，出现其中四个排第二，以此类推，希望查出如下的结果：
I大炮 出现个数
1 出现5个
2 出现4个
4 出现4个
3 出现3个
5 出现1个
谢谢大家！！！
------解决方案--------------------
- SQL code
分段截取if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_GetStr]GO--分段截取函数CREATE FUNCTION dbo.f_GetStr(@s varchar(8000), --包含多个数据项的字符串@pos int, --要获取的数据项的位置@split varchar(10) --数据分隔符)RETURNS varchar(100)ASBEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int SELECT @splitlen=LEN(@split+'a')-2 WHILE @pos>1 AND CHARINDEX(@split,@[email protected])>0 SELECT @[email protected], @s=STUFF(@s,1,CHARINDEX(@split,@[email protected])[email protected],'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@[email protected])-1),''))ENDGOselect dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
------解决方案--------------------
- SQL code
select '汽车' as s,count(*) as ct from tb where replace(keys,'%',',') like '%汽车%'union allselect '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%'union allselect '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%'union allselect '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%'union allselect '火箭' as s,count(*) as ct from tb where replace(keys,'%',',') like '%火箭%'order by 2 desc
------解决方案--------------------
- SQL code
create table tb(id int,keys nvarchar(20))insert into tb select 1,'汽车%轮船%轮船%大炮%坦克%火箭'insert into tb select 2,'轮船%大炮%坦克%火箭'insert into tb select 3,'大炮%坦克%火箭'insert into tb select 4,'轮船%轮船%大炮%坦克'insert into tb select 5,'坦克'goselect '汽车' as s,count(*) as ct from tb where replace(keys,'%',',') like '%汽车%'union allselect '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%'union allselect '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%'union allselect '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%'union allselect '火箭' as s,count(*) as ct from tb where replace(keys,'%',',') like '%火箭%'order by 2 desc/*s ct---- -----------坦克 5大炮 4火箭 3轮船 3汽车 1(5 行受影响)*/godrop table tb
------解决方案--------------------
- SQL code
create table tb(id int,cname varchar(100))insert into tbselect 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union allselect 2 ,'轮船%大炮%坦克%火箭' union allselect 3 ,'大炮%坦克%火箭' union allselect 4 ,'轮船%轮船%大炮%坦克' union allselect 5 ,'坦克'godeclare @jiansuo varchar(100)set @jiansuo = '汽车,轮船,轮船,大炮,坦克'select id,sum(case when charindex(cname,@jiansuo)>0 then 1 else 0 end) cntfrom( select a.id,substring(a.cname,b.number,charindex('%',a.cname+'%',b.number)-b.number) cname from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.cname) and substring('%'+a.cname,b.number,1) = '%')tgroup by idorder by cnt descdrop table tb/******************id cnt----------- -----------1 54 42 33 25 1(5 行受影响)
------解决方案--------------------
- SQL code
create table tb(id int,cname varchar(100))insert into tbselect 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union allselect 2 ,'轮船%大炮%坦克%火箭' union allselect 3 ,'大炮%坦克%火箭' union allselect 4 ,'轮船%轮船%大炮%坦克' union allselect 5 ,'坦克'godeclare @jiansuo varchar(100)set @jiansuo = '汽车,轮船,轮船,大炮,坦克'select id,sum(case when charindex(','+cname+',',',[email protected]+',')>0 then 1 else 0 end) cntfrom( select a.id,substring(a.cname,b.number,charindex('%',a.cname+'%',b.number)-b.number) cname from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.cname) and substring('%'+a.cname,b.number,1) = '%')tgroup by idorder by cnt descdrop table tb/********************id cnt----------- -----------1 54 42 33 25 1(5 行受影响)