求SQL:
WARD_NOPRO_NOEVA_LEVEL
10206 30 A
10206 30 B
10207 31 A
10207 31 B
10207 31 c
表2
PRO_NO EVA_LEVEL
30 A
30 B
30 C
31 A
31 B
31 C
31 D
需要得到的表为:
WARD_NOPRO_NOEVA_LEVEL
10206 30 A
10206 30 B
10206 30 C
10207 31 A
10207 31 B
10207 31 C
10207 31 D
[解决办法]
完整的:
create table 表1(WARD_NO int,PRO_NOint,EVA_LEVEL varchar(10))
insert into 表1
select 10206 , 30, 'A'union all
select 10206 , 30, 'B'union all
select 10207 , 31, 'A'union all
select 10207, 31, 'B'union all
select 10207, 31, 'c'
create table 表2(PRO_NO Int, EVA_LEVELvarchar(20))
insert into 表2
select 30, 'A'union all
select 30, 'B'union all
select 30, 'C'union all
select 31, 'A'union all
select 31, 'B'union all
select 31, 'C'union all
select 31, 'D'
go
select t.WARD_NO ,t2.PRO_NO,t2.EVA_LEVEL
from 表2 t2
inner join
(
select distinct WARD_NO,PRO_NO
from 表1
)t
on t.PRO_NO = t2.PRO_NO
left join 表1 t1
on t2.PRO_NO = t1.PRO_NO
and t2.EVA_LEVEL = t1.EVA_LEVEL
/*
WARD_NOPRO_NOEVA_LEVEL
1020630A
1020630B
1020630C
1020731A
1020731B
1020731C
1020731D
*/