字段1 字段2 字段3 字段4 字段5 字段6
1, 11, 白云, 2010,06, 4,7,6,9,10,5,0 6,6
1, 11, 白云, 2010,06, 4,7,9,10,1,0,6,6
1, 2, 白云, 2010,06, 4,7,6,9,1,0,1,1
学校1, 11, 白云, 2010,06, 14,15,17,6,9,10,13 39,39
学校1, 11, 白云, 2010,06, 4,7,6,9,10,2 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,6,9,10 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,14,15,17,6,9,10,6 39,39
学校1, 11, 白云, 2010,06, 5,7,6,9 39,39
select * from table1 where 字段5=变量 如:4怎么才能把字段5包含 4 的都找出啦
1, 11, 白云, 2010,06, 4,7,6,9,10,5,0 6,6
1, 11, 白云, 2010,06, 4,7,9,10,1,0,6,6
1, 2, 白云, 2010,06, 4,7,6,9,1,0,1,1
学校1, 11, 白云, 2010,06, 14,15,17,6,9,10,13 39,39
学校1, 11, 白云, 2010,06, 4,7,6,9,10,2 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,6,9,10 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,14,15,17,6,9,10,6 39,39
学校1, 11, 白云, 2010,06, 5,7,6,9 39,39
select * from table1 where 字段5=变量 如:4怎么才能把字段5包含 4 的都找出啦
if object_id('[TB]') is not null drop table [TB]
create table [TB]([字段1] varchar(6),[字段2] varchar(3),[字段3] varchar(5),[字段4] varchar(8),[字段5] varchar(31),[字段6] varchar(5))
insert [TB]
select '1,','11,','白云,','2010,06,','4,7,6,9,10,5,0','6,6' union all
select '1,','11,','白云,','2010,06,','4,7,9,10,1,0,6,6',null union all
select '1,','2,','白云,','2010,06,','4,7,6,9,1,0,1,1',null union all
select '学校1,','11,','白云,','2010,06,','14,15,17,6,9,10,13','39,39' union all
select '学校1,','11,','白云,','2010,06,','4,7,6,9,10,2','39,39' union all
select '学校1,','11,','白云,','2010,06,','4,7,5,6,1,2,6,6,9,10','39,39' union all
select '学校1,','11,','白云,','2010,06,','4,7,5,6,1,2,6,14,15,17,6,9,10,6','39,39' union all
select '学校1,','11,','白云,','2010,06,','5,7,6,9','39,39'select * from [TB]
select * from tb where [字段5] like '%4%'/*
字段1 字段2 字段3 字段4 字段5 字段6
------ ---- ----- -------- ------------------------------- -----
1, 11, 白云, 2010,06, 4,7,6,9,10,5,0 6,6
1, 11, 白云, 2010,06, 4,7,9,10,1,0,6,6 NULL
1, 2, 白云, 2010,06, 4,7,6,9,1,0,1,1 NULL
学校1, 11, 白云, 2010,06, 14,15,17,6,9,10,13 39,39
学校1, 11, 白云, 2010,06, 4,7,6,9,10,2 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,6,9,10 39,39
学校1, 11, 白云, 2010,06, 4,7,5,6,1,2,6,14,15,17,6,9,10,6 39,39(7 行受影响)
*/
declare @i varchar(10)
set @i='4'
select * from table1 where charindex(','+ltrim(@i)+',',','+字段5+',')>0
if object_id('tb') is not null
drop table tb
go
create table tb(字段1 varchar(20), 字段2 int,字段3 varchar(20), 字段4 varchar(20) ,字段5 varchar(40),字段6 varchar(20))
insert into tb select '1', 11, '白云', '2010,06', '4,7,6,9,10,5,0','6,6'
union all select '1', 11, '白云', '2010,06', '4,7,9,10,1,0','6,6'
union all select '1', 2, '白云', '2010,06', '4,7,6,9,1,0','1,1'
union all select '学校1', 11, '白云', '2010,06', '14,15,17,6,9,10,13','39,39'
union all select '学校1', 11, '白云', '2010,06', '4,7,6,9,10,2','39,39'
union all select '学校1', 11, '白云', '2010,06', '4,7,5,6,1,2,6,6,9,10','39,39'
union all select '学校1', 11, '白云', '2010,06', '4,7,5,6,1,2,6,14,15,17,6,9,10,6','39,39'
union all select '学校1', 11, '白云', '2010,06', '5,7,6,9','39,39'
go
-- 测试declare @input varchar(20)
set @input = '4'select * from tb where charindex(','+@input + ',' ,',' + 字段5+',')>0/*
字段1 字段2 字段3 字段4 字段5 字段6
-------------------- ----------- -------------------- -------------------- ---------------------------------------- --------------------
1 11 白云 2010,06 4,7,6,9,10,5,0 6,6
1 11 白云 2010,06 4,7,9,10,1,0 6,6
1 2 白云 2010,06 4,7,6,9,1,0 1,1
学校1 11 白云 2010,06 4,7,6,9,10,2 39,39
学校1 11 白云 2010,06 4,7,5,6,1,2,6,6,9,10 39,39
学校1 11 白云 2010,06 4,7,5,6,1,2,6,14,15,17,6,9,10,6 39,39(6 行受影响)
*/