表tba 有字段deptcd
内容如 C3100 C6100
表tbb有字段purview
如有字段
C3100,C3200本想select * from tba where deptcd in (select purview from tbb )
这样会得到C3100
但是选择出来是空的,
得怎么写才能出来
C3100就是如果tba的deptcd只要包含在tbb的purview
就符合条件
内容如 C3100 C6100
表tbb有字段purview
如有字段
C3100,C3200本想select * from tba where deptcd in (select purview from tbb )
这样会得到C3100
但是选择出来是空的,
得怎么写才能出来
C3100就是如果tba的deptcd只要包含在tbb的purview
就符合条件
from tba a
join deptcd b
on charindex(','+a.deptcd+',',','+b.purview+',')>0
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 10:27:56
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tba]
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([deptcd] varchar(5))
insert [tba]
select 'C3100' union all
select 'C6100'
--> 测试数据:[tbb]
if object_id('[tbb]') is not null drop table [tbb]
go
create table [tbb]([purview] varchar(11))
insert [tbb]
select 'C3100,C3200'
--------------开始查询--------------------------
select * from [tba] a ,tbb b where charindex(','+a.deptcd+',',','+b.purview+',')>0
----------------结果----------------------------
/* deptcd purview
------ -----------
C3100 C3100,C3200(1 行受影响)
*/
where exists (select 1
from tbb where charindex(deptcd, purview ,1)>0)
from tba a
join deptcd b
on charindex(','+a.deptcd+',',','+b.purview+',')>0