◤100分，疯掉了◥关于按照出现的词频统计并排序的有关问题

分段截取
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)
AS
BEGIN
    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),''))
END
GO

select 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 all
select '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%'
union all
select '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%'
union all
select '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%'
union all
select '火箭' 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,'坦克'
go
select '汽车' as s,count(*) as ct from tb where replace(keys,'%',',') like '%汽车%'
union all
select '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%'
union all
select '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%'
union all
select '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%'
union all
select '火箭' as s,count(*) as ct from tb where replace(keys,'%',',') like '%火箭%'
order by 2 desc
/*
s    ct
---- -----------
坦克   5
大炮   4
火箭   3
轮船   3
汽车   1

(5 行受影响)

*/
go
drop table tb

------解决方案--------------------

SQL code

create table tb(id int,cname varchar(100))
insert into tb
select 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union all
select 2 ,'轮船%大炮%坦克%火箭' union all
select 3 ,'大炮%坦克%火箭' union all
select 4 ,'轮船%轮船%大炮%坦克' union all
select 5 ,'坦克'
go

declare @jiansuo varchar(100)
set @jiansuo = '汽车,轮船,轮船,大炮,坦克'

select id,sum(case when charindex(cname,@jiansuo)>0 then 1 else 0 end) cnt
from(
    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) = '%'
)t
group by id
order by cnt desc

drop table tb

/******************

id          cnt
----------- -----------
1           5
4           4
2           3
3           2
5           1

(5 行受影响)

------解决方案--------------------

SQL code

create table tb(id int,cname varchar(100))
insert into tb
select 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union all
select 2 ,'轮船%大炮%坦克%火箭' union all
select 3 ,'大炮%坦克%火箭' union all
select 4 ,'轮船%轮船%大炮%坦克' union all
select 5 ,'坦克'
go

declare @jiansuo varchar(100)
set @jiansuo = '汽车,轮船,轮船,大炮,坦克'

select id,sum(case when charindex(','+cname+',',','+@jiansuo+',')>0 then 1 else 0 end) cnt
from(
    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) = '%'
)t
group by id
order by cnt desc

drop table tb

/********************

id          cnt
----------- -----------
1           5
4           4
2           3
3           2
5           1

(5 行受影响)