sql²éѯ - MS-SQL Server / ÒÉÄÑÎÊÌâ

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Öµ£¿£¿

---

½á¹ûÊÇʲô

---

ûÃ÷°×ʲôÒâ˼

---

SQL code:
select userid
from tb
where charindex(',3,',','+str+',')>0
group by userid

---

SQL code:
select * from tb where charindex(',3,',','+str+',')>0

---

SQL code:


create table aa
(
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 (
selec