◤100分，疯掉了◥关于按照出现的词频统计并排序的问题
我现有一张表,结构如下，我想输入几个词，如何统计出这几个词在每条记录中出现的个数，并按个数进行倒序排序，
表的内容：
I大炮 Keys
1 汽车%轮船%轮船%大炮%坦克%火箭
2 轮船%大炮%坦克%火箭
3 大炮%坦克%火箭
4 轮船%轮船%大炮%坦克
5 坦克
比如，我输入汽车,轮船,轮船,大炮,坦克 五个词进行检索，全出现的排第一，出现其中四个排第二，以此类推，希望查出如下的结果：
I大炮 出现个数
1 出现5个
2 出现4个
4 出现4个
3 出现3个
5 出现1个
谢谢大家！！！
[解决办法]
分段截取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,@s+@split)>0 SELECT @pos=@pos-1, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))ENDGOselect dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
[解决办法]
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
[解决办法]
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
[解决办法]
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 行受影响)
[解决办法]
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 行受影响)
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-11-09 11:44:26-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([I大炮] int,[Keys] varchar(29))insert [tb]select 1,'汽车%轮船%轮船%大炮%坦克%火箭' union allselect 2,'轮船%大炮%坦克%火箭' union allselect 3,'大炮%坦克%火箭' union allselect 4,'轮船%轮船%大炮%坦克' union allselect 5,'坦克'--------------开始查询--------------------------declare @jiansuo varchar(100)set @jiansuo = '汽车,轮船,轮船,大炮,坦克'select I大炮,'出现'+ltrim(sum(case when charindex(cname,@jiansuo)>0 then 1 else 0 end))+'次' as 出现个数from( select a.I大炮,substring(a.Keys,b.number,charindex('%',a.Keys+'%',b.number)-b.number) cname from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.Keys) and substring('%'+a.Keys,b.number,1) = '%')tgroup by I大炮----------------结果----------------------------/* I大炮 出现个数----------- ------------------1 出现5次2 出现3次3 出现2次4 出现4次5 出现1次(5 行受影响)*/
[解决办法]
分拆再求和--> --> (Roy)生成測試數據 if not object_id('Tab') is null drop table TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'GoSQL2000用辅助表:if object_id('Tempdb..#Num') is not null drop table #Numgoselect top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Num bwhere charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','SQL2005用Xml:select a.COl1,b.Col2from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)aouter apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))bSQL05用CTE:;with roy as (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tabunion allselect Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'')select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:/*Col1 COl2----------- -----1 a1 b1 c2 d2 e3 f*/
[解决办法]
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([Col1] int,[Col2] nvarchar(17))Insert #Tselect 1,N'汽车%轮船%轮船%大炮%坦克%火箭' union allselect 2,N'轮船%大炮%坦克%火箭' union allselect 3,N'大炮%坦克%火箭' union allselect 4,N'轮船%轮船%大炮%坦克' union allselect 5,N'坦克'Goselect b.Col2,COUNT(a.COl1) AS 記錄數from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,'%','</v><v>')+'</v></root>') from #T)aouter apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b GROUP BY b.Col2 ORDER BY 2 DESC /* Col2 記錄數坦克 5轮船 5大炮 4火箭 3汽车 1*/
[解决办法]
create table #tb(I大炮 int,Keys varchar(100))insert into #tbselect 1,'汽车%轮船%轮船%大炮%坦克%火箭'union allselect 2,'轮船%大炮%坦克%火箭'union allselect 3,'大炮%坦克%火箭'union allselect 4,'轮船%轮船%大炮%坦克'union allselect 5,'坦克'select a.I大炮,'出现'+convert(varchar(10),count(id))+'个' as 出现个数 from (select I大炮,aa=CONVERT(xml,'<v>'+REPLACE(Keys,'%','</v><v>')+'</v>') from #tb)a outer apply (select id=N.v.value('.','varchar(20)') from a.aa.nodes('/v')N (v))b group by a.I大炮
[解决办法]
select a.I大炮,'出现'+convert(varchar(10),count(id))+'个' as 出现个数 from (select I大炮,aa=CONVERT(xml,'<v>'+REPLACE(Keys,'%','</v><v>')+'</v>') from #tb)a outer apply (select id=N.v.value('.','varchar(20)') from a.aa.nodes('/v')N (v))b group by a.I大炮 order by 出现个数 descI大炮 出现个数----------- ----------------1 出现6个2 出现4个4 出现4个3 出现3个5 出现1个