PersonID AttnIDs
---------------- --------
18 13 10
15 16 15
15 9
PersonID为int型,AttnIDs为varchar型.统计数字在这张表中出现的次数.(如第二行数据中出现2个15,那么只算其出现一次)做出最后结果为ID 次数
---------------- --------
18 1
13 1
10 1
15 2
16 1
9 1求大神解决.或者给我一个思路。
---------------- --------
18 13 10
15 16 15
15 9
PersonID为int型,AttnIDs为varchar型.统计数字在这张表中出现的次数.(如第二行数据中出现2个15,那么只算其出现一次)做出最后结果为ID 次数
---------------- --------
18 1
13 1
10 1
15 2
16 1
9 1求大神解决.或者给我一个思路。
--不清楚你的18 13 10这些数字是分开放在PersonID和AttnIDs呢,还是全部放在AttnIDs,我只能猜一个了if object_id('test') is not null drop table test
go
create table test(PersonID int,AttnIDs varchar(20))
go
insert into test
select 18,'13 10' union all
select 15,'16 15' union all
select 15,'9'
go
declare @xml xml,@str varchar(max)
select @str=isnull(@str,'')+'<x>'+convert(varchar(5),PersonID)+'</x><x>'+replace(AttnIDs,' ','</x><x>')+'</x>' from test
select @xml=convert(xml,@str);with cte as
(
select N.v.value('.','varchar(10)') ID
from @xml.nodes('/x') N(v)
)select ID,count(ID) cnt from cte
group by ID/*
ID cnt
---------- -----------
10 1
13 1
15 3
16 1
18 1
9 1
*/
if object_id('test') is not null drop table test
go
create table test(PersonID int,AttnIDs varchar(20))
go
insert into test
select 18,'13 10' union all
select 15,'16 15' union all
select 15,'9'
go
declare @xml xml,@str varchar(max)
select @str=isnull(@str,'')+'<x>'+convert(varchar(5),PersonID)+'</x><x>'+replace(replace(AttnIDs,convert(varchar(5),PersonID),''),' ','</x><x>')+'</x>' from test
select @xml=convert(xml,@str);with cte as
(
select N.v.value('.','varchar(10)') ID
from @xml.nodes('/x') N(v)
)select ID,count(ID) cnt from cte
group by ID/*(3 行受影响)
ID cnt
---------- -----------
1
10 1
13 1
15 2
16 1
18 1
9 1(7 行受影响)*/
if object_id('test') is not null drop table test
go
create table test(PersonID int,AttnIDs varchar(20))
go
insert into test
select 18,'13 10' union all
select 15,'16 15' union all
select 15,'9'
go
declare @xml xml,@str varchar(max)
select @str=isnull(@str,'')+'<x>'+convert(varchar(5),PersonID)+'</x><x>'+replace(replace(AttnIDs,convert(varchar(5),PersonID),''),' ','</x><x>')+'</x>' from test
select @xml=convert(xml,@str);with cte as
(
select N.v.value('.','varchar(10)') ID
from @xml.nodes('/x') N(v)
)select ID,count(ID) cnt from cte
where ID<>''
group by ID/*(3 行受影响)
ID cnt
---------- -----------
10 1
13 1
15 2
16 1
18 1
9 1(6 行受影响)*/
通过你的方法解决了,非常感谢!能推荐点书看看吗,sql语句学的不精。