有t1,t2表t1表中字段 a ,包含 t2表中的主键ID集合
如:
t1表
id name a
1 test 1,2,3
2 test2 2,3
3 test 1,3
4 test 1t2表
id name
1 t1
2 t2
3 t3
问题:
我怎么查询t1中所有含有 t2=1的数据
如:
t1表
id name a
1 test 1,2,3
2 test2 2,3
3 test 1,3
4 test 1t2表
id name
1 t1
2 t2
3 t3
问题:
我怎么查询t1中所有含有 t2=1的数据
from t1 t
where exists(select 1 from t2 where charindex(','+ltrim(id)+',',','+t.a+',')>0 and id=1)?
SELECT *
FROM t1 AS a
WHERE ','+a+',' LIKE '%,1,%'
from t1
where ','+a+',' like '%,1,%'?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 09:23:15
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([id] int,[name] varchar(5),[a] varchar(5))
insert [t1]
select 1,'test','1,2,3' union all
select 2,'test2','2,3' union all
select 3,'test','1,3' union all
select 4,'test','1'
--> 测试数据:[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[name] varchar(2))
insert [t2]
select 1,'t1' union all
select 2,'t2' union all
select 3,'t3'
--------------开始查询--------------------------
select
*
from
t1 t
where
exists(select 1 from t2 where charindex(','+ltrim(id)+',',','+t.a+',')>0 and id=1)
----------------结果----------------------------
/* id name a
----------- ----- -----
1 test 1,2,3
3 test 1,3
4 test 1(3 行受影响)*/
from t1
join t2 on charindex(','+ltrim(rtrim(t2.id))+',',','+t1.a+',' )>0
where t2.id=1
select t1.*
from t1
join t2 on charindex(','+ltrim(rtrim(t2.id))+',',','+t1.a+',' )>0
where t2.id=1id name a
----------- ----- -----
1 test 1,2,3
3 test 1,3
4 test 1(3 行受影响)