27,579
社区成员
发帖
与我相关
我的任务
分享
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from test where charindex('2,',DepartID)>0
--返回所有记录
--想要的结果是只返回,前两条记录
--高手是否还在
select * from test
where charindex(',22,',','+DepartID+',')>0
返回结果 (0 行受影响)
--3楼也可以
--只是中间加好稍显多余
--我也只想到这种方法
select * from #test where charindex('1,',DepartID)>0
--没想到前后都加逗号,还是有差距啊
--以上回复经过测试,4楼,5楼可行
--其余要是返回为1的就会有问题
select * from #test where charindex(',1,',DepartID)>0
select * from #test where charindex('1,',DepartID)>0
create table #test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO #test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from #test where charindex(',2,',DepartID)>0
--返回所有记录
--想要的结果是只返回,前两条记录
Id DepartID
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3
2 1,2,4
(2 行受影响)
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
select * from test where charindex(',2,',','+DepartID+',')>0
/*
Id DepartID
1 1,2,3
2 1,2,4
*/
select * from test
where charindex(',2,',','+DepartID+',')>0
/*
Id DepartID
----------- --------------------------------------------------
1 1,2,3
2 1,2,4
(2 row(s) affected)
*/
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from test where charindex(','+'2,',','+DepartID+',')>0
/*
Id DepartID
1 1,2,3
2 1,2,4
*/
--返回所有记录
--想要的结果是只返回,前两条记录
select top 2 * from #test where charindex('2,',DepartID)>0
select * from test1 where charindex(',2,',DepartID)>0
Id DepartID
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3
2 1,2,4
(2 行受影响)