62,046
社区成员
发帖
与我相关
我的任务
分享
;with cte as
(select SUBSTRING(xueke,0,charindex(',',xueke)) xueke,hits from T1218
union all select RIGHT(xueke,len(xueke)-charindex(',',xueke)) xueke,hits from T1218)
select xueke,SUM(hits) from cte where xueke<>'' group by xueke order by xueke desc
/* xueke hits
语文 5
英语 3
物理 10
数学 2
生物 6
化学 7 */
create table #tb([xueke] varchar(50),[hits] int)
insert #tb
select '数学,语文',2 union all
select '语文,英语',3 union all
select '化学,物理',4 union all
select '物理,生物',6 union all
select '化学',3
select [xueke],SUM(hits) as hits
from
(SELECT SUBSTRING([xueke],number,CHARINDEX(',',[xueke]+',',number)-number) as [xueke],a.hits
from #tb a, master..spt_values
where number >=1 and type='p'
and number<len([xueke]) and substring(','+[xueke],number,1)=','
)t
group by [xueke]
order by 2
/*
xueke hits
数学 2
英语 3
语文 5
生物 6
化学 7
物理 10
*/
--实现split功能 的函数
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
go
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([xueke] varchar(50),[hits] int)
insert [a]
select '数学,语文',2 union all
select '语文,英语',3 union all
select '化学,物理',4 union all
select '物理,生物',6 union all
select '化学',3
if object_id('[b]') is not null drop table [b]
go
create table [b]([xueke] varchar(50),[hits] int)
select row_number() over(order by xueke ) as rn,* into #a from a
declare @i int ,@j int ,@k varchar(10),@l int
set @i =1
select @j=max(rn) from #a
while(@i<=@j)
begin
select @k=[xueke],@l= [hits] from #a where rn =@i
insert into b select a,@l from dbo.f_split(@k,',')
set @i=@i+1
end
select xueke ,sum(hits) as hits from b group by xueke
xueke hits
-------------------------------------------------- -----------
化学 7
生物 6
数学 2
物理 10
英语 3
语文 5
(6 行受影响)