select * from ta a,V_bumen b where charindex(left(b.zy_id),a.zy_id)>0
--更正 select * from ta a,V_bumen b where charindex(left(b.zy_id,2),a.zy_id)>0
--> 测试数据: #T1 if object_id('tempdb.dbo.#T1') is not null drop table #T1 create table #T1 (bmid varchar(2),bumen varchar(6),zy_id varchar(20)) insert into #T1 select 'IT','电脑部','02,CF,10' union all select 'HR','人事部','BH,11,08' --> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A create table #A (id int,job varchar(12),zyid varchar(5)) insert into #A select 1,'0002315-1-2','02-11' union all select 2,'0001256-5-6','CF-05' union all select 3,'0009659-11-3','BH-23' union all select 4,'0008956-2-6','11-05' union all select 5,'0006985-12-9','10-09' select top 2 #A.* from #T1 T join #A on charindex(','+left(#A.zyid,2)+',',','+zy_id+',')>0 where bmid = 'it'id job zyid ----------- ------------ ----- 1 0002315-1-2 02-11 2 0001256-5-6 CF-05(2 行受影响)
--> 测试数据: #T1 if object_id('tempdb.dbo.#T1') is not null drop table #T1 create table #T1 (bmid varchar(2),bumen varchar(6),zy_id varchar(20)) insert into #T1 select 'IT','电脑部','02,CF,10' union all select 'HR','人事部','BH,11,08' --> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A create table #A (id int,job varchar(12),zyid varchar(5)) insert into #A select 1,'0002315-1-2','02-11' union all select 2,'0001256-5-6','CF-05' union all select 3,'0009659-11-3','BH-23' union all select 4,'0008956-2-6','11-05' union all select 5,'0006985-12-9','10-09' select top 2 #A.* from #T1 T join #A on charindex(','+left(#A.zyid,2)+',',','+zy_id+',')>0 where bmid = 'it' order by #A.id descid job zyid ----------- ------------ ----- 5 0006985-12-9 10-09 2 0001256-5-6 CF-05(2 行受影响)
select * from 表A where charindex(left(zyid,2),(select zy_id V_bumen where bmid='IT'))>0
select * from 表A where charindex(left(zyid,2),(select zy_id from V_bumen where bmid='IT'))>0 刚才的少个from
if object_id('v_bumen') is not null drop table v_bumen create table v_bumen ( bmid char(2), bumen varchar(10), zy_id varchar(50) )if object_id('a') is not null drop table a create table a ( id int, job varchar(20), zyid varchar(20) )insert into v_bumen select 'it','电脑部','02,cf,10' union all select 'hr','人事部','bh,11,08'insert into a select 1,'0002315-1-A','02-11' union all select 2,'0001256-5-F','CF-05' union all select 3,'0009659-11-C','BH-23'union all select 4,'0008956-2-G','11-05' union all select 5,'0006985-12-D','10-09' union all select 6,'0000000-12-d','22-22' select a.* from a,v_bumen where charindex(left(zyid,2),zy_id)>0 and bmid='it'
from ta a,V_bumen b
where charindex(left(b.zy_id),a.zy_id)>0
select *
from ta a,V_bumen b
where charindex(left(b.zy_id,2),a.zy_id)>0
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (bmid varchar(2),bumen varchar(6),zy_id varchar(20))
insert into #T1
select 'IT','电脑部','02,CF,10' union all
select 'HR','人事部','BH,11,08'
--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,job varchar(12),zyid varchar(5))
insert into #A
select 1,'0002315-1-2','02-11' union all
select 2,'0001256-5-6','CF-05' union all
select 3,'0009659-11-3','BH-23' union all
select 4,'0008956-2-6','11-05' union all
select 5,'0006985-12-9','10-09'
select top 2 #A.* from #T1 T
join #A on charindex(','+left(#A.zyid,2)+',',','+zy_id+',')>0
where bmid = 'it'id job zyid
----------- ------------ -----
1 0002315-1-2 02-11
2 0001256-5-6 CF-05(2 行受影响)
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (bmid varchar(2),bumen varchar(6),zy_id varchar(20))
insert into #T1
select 'IT','电脑部','02,CF,10' union all
select 'HR','人事部','BH,11,08'
--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,job varchar(12),zyid varchar(5))
insert into #A
select 1,'0002315-1-2','02-11' union all
select 2,'0001256-5-6','CF-05' union all
select 3,'0009659-11-3','BH-23' union all
select 4,'0008956-2-6','11-05' union all
select 5,'0006985-12-9','10-09'
select top 2 #A.* from #T1 T
join #A on charindex(','+left(#A.zyid,2)+',',','+zy_id+',')>0
where bmid = 'it'
order by #A.id descid job zyid
----------- ------------ -----
5 0006985-12-9 10-09
2 0001256-5-6 CF-05(2 行受影响)
where charindex(left(zyid,2),(select zy_id V_bumen where bmid='IT'))>0
where charindex(left(zyid,2),(select zy_id from V_bumen where bmid='IT'))>0
刚才的少个from
if object_id('v_bumen') is not null
drop table v_bumen
create table v_bumen
(
bmid char(2),
bumen varchar(10),
zy_id varchar(50)
)if object_id('a') is not null
drop table a
create table a
(
id int,
job varchar(20),
zyid varchar(20)
)insert into v_bumen
select 'it','电脑部','02,cf,10' union all
select 'hr','人事部','bh,11,08'insert into a
select 1,'0002315-1-A','02-11' union all
select 2,'0001256-5-F','CF-05' union all
select 3,'0009659-11-C','BH-23'union all
select 4,'0008956-2-G','11-05' union all
select 5,'0006985-12-D','10-09' union all
select 6,'0000000-12-d','22-22'
select a.* from a,v_bumen where charindex(left(zyid,2),zy_id)>0 and bmid='it'