table aa
(
id int ,--自动编号
userid int ,--用户id
prcid int ,--产品id
str varchar(2000)---数字字符串
)
id userid str
1 2 '1,2,3,4,5'
2 3 ',23,4,5'
3 4 '1,2,5,4'
已经获取在str列中最小唯一值是3
如何根据3获取3所在列的id值或userid值??
(
id int ,--自动编号
userid int ,--用户id
prcid int ,--产品id
str varchar(2000)---数字字符串
)
id userid str
1 2 '1,2,3,4,5'
2 3 ',23,4,5'
3 4 '1,2,5,4'
已经获取在str列中最小唯一值是3
如何根据3获取3所在列的id值或userid值??
from tb
where charindex(',3,',','+str+',')>0
group by userid
(
id int ,--自动编号
userid int ,--用户id
prcid int ,--产品id
str varchar(2000)---数字字符串
)
insert aa (id ,userid ,str)
select 1 ,2, '1,2,3,4,5' union all
select 2, 3, '23,4,5' union all
select 3, 4, '1,2,5,4'with cte as(
select
a.id,userid,b.value
from
(select id,userid,str=convert(xml,'<root><row>'+replace(str,',','</row><row>')+'</row></root>') from aa)a
outer apply
(select value=T.C.value('.','nvarchar(100)') from a.str.nodes('/root/row')T(C))b)select top 1 * from (
select * from cte a where exists(select 1 from cte where a.value=value group by value having count(1)=1)
) a
order by cast(value as int) id userid value
----------- ----------- ----------------------------------------------------------------------------------------------------
1 2 3(1 行受影响)
select * from aa where ','+str+',' like '%,3,%'
select * from aa where charindex(',3,' , ','+str+',') > 0