表A中有两个字段:ID和SONID,有如下三条记录:
ID SonID
4 101,25,42,68,74
72 64,172,89,26,79
8 52,79,86,8,167其中SonID字段内容为被“,”隔开的字符串。要查询出:ID被SonID包含的记录(比较SonID中“,”隔开的每段字符串)。上面的例子只有第三条符合,因为SonID的第四段字符串为“8”,而前两条记录中,SonID虽然都有4或72字符,但不是被“,”隔开的整段字符串。求查询语句。like、charindex或patindex好像不能解决。
ID SonID
4 101,25,42,68,74
72 64,172,89,26,79
8 52,79,86,8,167其中SonID字段内容为被“,”隔开的字符串。要查询出:ID被SonID包含的记录(比较SonID中“,”隔开的每段字符串)。上面的例子只有第三条符合,因为SonID的第四段字符串为“8”,而前两条记录中,SonID虽然都有4或72字符,但不是被“,”隔开的整段字符串。求查询语句。like、charindex或patindex好像不能解决。
select * from tb where charindex(','+ltrim(id)+',',','+sonid+',')>0
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 09:03:15
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[SonID] varchar(15))
insert [tb]
select 4,'101,25,42,68,74' union all
select 72,'64,172,89,26,79' union all
select 8,'52,79,86,8,167'
--------------开始查询--------------------------
select * from tb where charindex(','+ltrim(id)+',',','+sonid+',')>0
----------------结果----------------------------
/*
ID SonID
----------- ---------------
8 52,79,86,8,167(所影响的行数为 1 行)*/
if object_id('tb')is not null drop table tb
create table tb
(id varchar(5),
sonID varchar(50))insert tb
select '4','101,25,42,68,74 ' union all
select '72','64,172,89,26,79' union all
select '8','52,79,86,8,167' select * from tb
where charindex(','+ltrim(id)+',',','+sonID+',')>0
drop table tb
go
create table tb(id int,SonID varchar(50))
insert into tb select 4, '101,25,42,68,74'
union all select 72, '64,172,89,26,79'
union all select 8, '52,79,86,8,167'
union all select 8, '8,79,86,65,167'
union all select 8, '52,79,86,87,8'
union all select 8, '52,79,86,88,167'goselect * from tb where charindex(','+ltrim(id)+',',','+sonid+',')>0/*id SonID
----------- --------------------------------------------------
8 52,79,86,8,167
8 8,79,86,65,167
8 52,79,86,87,8(3 行受影响)*/
drop table tb
go
create table tb(id int,SonID varchar(50))
insert into tb select 4, '101,25,42,68,74'
union all select 72, '64,172,89,26,79'
union all select 8, '52,79,86,8,167'
union all select 8, '8,79,86,65,167'
union all select 8, '52,79,86,87,8'
union all select 8, '52,79,86,88,167' go select * from tb where charindex(','+ltrim(id)+',',','+sonid+',')>0 /*id SonID
----------- --------------------------------------------------
8 52,79,86,8,167
8 8,79,86,65,167
8 52,79,86,87,8 (3 行受影响) */
if object_id('tb') is not null
drop table tb
go
create table tb(id int,SonID varchar(50))
insert into tb select 4, '101,25,42,68,74'
union all select 72, '64,172,89,26,79'
union all select 8 , '52,79,86, 8,167'
union all select 8, ' 8,79,86,65,167'
union all select 8 , '52,79,86,87,8 '
union all select 8, '52,79,86,88,167'goselect * from tb where charindex(','+ltrim(id)+',',','+replace(sonid,' ','')+',')>0
/*id SonID
----------- --------------------------------------------------
8 52,79,86, 8,167
8 8,79,86,65,167
8 52,79,86,87,8 (3 行受影响)
*/