22,206
社区成员
发帖
与我相关
我的任务
分享
select * from tba
where exists (select 1
from tbb where charindex(deptcd, purview ,1)>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 行受影响)
*/
select a.*
from tba a
join deptcd b
on charindex(','+a.deptcd+',',','+b.purview+',')>0
select * from tba where charindex(','+deptcd+',',','+purview+',')>0
select * from tba where charindex(',+deptcd+,',',+purview+,')>0