A表
empid name
A01 tom
A02 mary
A03 gary
B表
ClassID empid cname
C01 A01 english
C02 A01 math
C03 A02 physics
想要结果:
empid classid name cname
A01 C01 tom english
A02 C03 mary physics
A03 NULL gary NULL
正解一:
declare
@t1
table
(empid
varchar
(
10
),name
varchar
(
10
))
insert @t1 select ' A01 ' , ' tom '
insert @t1 select ' A02 ' , ' mary '
insert @t1 select ' A03 ' , ' gary '
declare @t2 table (classid varchar ( 10 ),empid varchar ( 10 ),cname varchar ( 10 ))
insert @t2 select ' C01 ' , ' A01 ' , ' english '
insert @t2 select ' C02 ' , ' A01 ' , ' math '
insert @t2 select ' C03 ' , ' A02 ' , ' physics '
select
a.empid,b1.classid,a.name,b1.cname
from @t1 a
left join @t2 b1
on a.empid = b1.empid and not exists ( select 1 from @t2 where empid = b1.empid and classid < b1.classid)
-- 结果
empid classid name cname
-- -------- ---------- ---------- ----------
A01 C01 tom english
A02 C03 mary physics
A03 NULL gary NULL
(所影响的行数为 3 行)
insert @t1 select ' A01 ' , ' tom '
insert @t1 select ' A02 ' , ' mary '
insert @t1 select ' A03 ' , ' gary '
declare @t2 table (classid varchar ( 10 ),empid varchar ( 10 ),cname varchar ( 10 ))
insert @t2 select ' C01 ' , ' A01 ' , ' english '
insert @t2 select ' C02 ' , ' A01 ' , ' math '
insert @t2 select ' C03 ' , ' A02 ' , ' physics '
select
a.empid,b1.classid,a.name,b1.cname
from @t1 a
left join @t2 b1
on a.empid = b1.empid and not exists ( select 1 from @t2 where empid = b1.empid and classid < b1.classid)
-- 结果
empid classid name cname
-- -------- ---------- ---------- ----------
A01 C01 tom english
A02 C03 mary physics
A03 NULL gary NULL
(所影响的行数为 3 行)
正确二:
declare
@t1
table
(empid
varchar
(
10
),name
varchar
(
10
))
insert @t1 select ' A01 ' , ' tom '
insert @t1 select ' A02 ' , ' mary '
insert @t1 select ' A03 ' , ' gary '
declare @t2 table (classid varchar ( 10 ),empid varchar ( 10 ),cname varchar ( 10 ))
insert @t2 select ' C01 ' , ' A01 ' , ' english '
insert @t2 select ' C02 ' , ' A01 ' , ' math '
insert @t2 select ' C03 ' , ' A02 ' , ' physics '
-- select 1 from @t2 tmp where empid=tmp.empid and classid<tmp.classid
-- select * from @t2 tmp where not exists (select 1 from @t2 where empid=tmp.empid and classid<tmp.classid)
select t1.empid,t2.classid,t1.name,t2.cname
from @t1 t1
left join ( select * from @t2 tmp where not exists ( select 1 from @t2 where empid = tmp.empid and classid < tmp.classid)) t2
on t1.empid = t2.empid
insert @t1 select ' A01 ' , ' tom '
insert @t1 select ' A02 ' , ' mary '
insert @t1 select ' A03 ' , ' gary '
declare @t2 table (classid varchar ( 10 ),empid varchar ( 10 ),cname varchar ( 10 ))
insert @t2 select ' C01 ' , ' A01 ' , ' english '
insert @t2 select ' C02 ' , ' A01 ' , ' math '
insert @t2 select ' C03 ' , ' A02 ' , ' physics '
-- select 1 from @t2 tmp where empid=tmp.empid and classid<tmp.classid
-- select * from @t2 tmp where not exists (select 1 from @t2 where empid=tmp.empid and classid<tmp.classid)
select t1.empid,t2.classid,t1.name,t2.cname
from @t1 t1
left join ( select * from @t2 tmp where not exists ( select 1 from @t2 where empid = tmp.empid and classid < tmp.classid)) t2
on t1.empid = t2.empid