A表
empid name
A01 tom
A02 mary
A03 garyB表
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 gary
SQL 语句如何写
empid name
A01 tom
A02 mary
A03 garyB表
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 gary
SQL 语句如何写
from t1 a
left outer join
t2 b
on a.empid=b.empid
from aleft join b
on a.empid=b.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 a.empid,b.classid,a.name,b.cname
from @t1 a
left outer join
@t2 b
on a.empid=b.empid/*
empid classid name cname
---------- ---------- ---------- ----------
A01 C01 tom english
A01 C02 tom math
A02 C03 mary physics
A03 NULL gary NULL(4 行受影响)*/
a.empid,b1.classid,a.name,b1.cname
from a
left join b b1
on a.empid=b1.empid and not exists (select 1 from b where empid=b1.empid and classid<b1.classid)
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,isnull(b.classid,'') classid,a.name,isnull(b.cname,'') cname
from @t1 a
left outer join
@t2 b
on a.empid=b.empid/*
empid classid name cname
---------- ---------- ---------- ----------
A01 C01 tom english
A01 C02 tom math
A02 C03 mary physics
A03 gary (4 行受影响)*/
select a.empid,b.classid,a.name,b.cname from a left join (select * from b bb
where not exists(select 1 from b where bb.empid=empid and bb.classId>classid)) b
on a.empid=b.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
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,isnull(b.classid,'') classid,a.name,isnull(b.cname,'') cname
from @t1 a
left outer join
@t2 b
on a.empid=b.empid
and not exists (select 1 from @t2 where empid=b.empid and classid<b.classid)/*
empid classid name cname
---------- ---------- ---------- ----------
A01 C01 tom english
A02 C03 mary physics
A03 gary
*/
select m.empid , isnull(n.ClassID,'') ClassID , m.name , isnull(n.cname,'') cname from A m
left join
(select t.* from b t where ClassID = (select min(ClassID) from b where empid = t.empid)) n
on m.empid = n.empid
from A t1
left join (select * from B tmp where not exists (select 1 from B where empid=tmp.empid and classid<tmp.classid)) t2
on t1.empid=t2.empid
insert A select 'A01' , 'tom'
insert A select 'A02' , 'mary'
insert A select 'A03' , 'gary'create table B(classid varchar(10),empid varchar(10),cname varchar(10))
insert B select 'C01' , 'A01' , 'english'
insert B select 'C02' , 'A01' , 'math'
insert B select 'C03' , 'A02' , 'physics'select m.empid , isnull(n.ClassID,'') ClassID , m.name , isnull(n.cname,'') cname from A m
left join
(select t.* from b t where ClassID = (select min(ClassID) from b where empid = t.empid)) n
on m.empid = n.empiddrop table A,B/*
empid ClassID name cname
---------- ---------- ---------- ----------
A01 C01 tom english
A02 C03 mary physics
A03 gary (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 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
/*
empid classid name cname
---------------------------------
A01 C01 tom english
A02 C03 mary physics
A03 NULL gary NULL
*/
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 into a select 'A01','tom '
insert into a select 'A02','mary'
insert into a select 'A03','gary'create table B(ClassID varchar(3),empid varchar(3),cname varchar(20))
insert into b select 'C01','A01','english'
insert into b select 'C02','A01','math '
insert into b select 'C03','A02','physics'
select * from a
select * from bselect a.empid,b.classid,a.name,b.cname
from a left join b
on a.empid=b.empidselect a.empid,min(b.classid) as classid,min(a.name) as name,min(b.cname) as cname
from a left join b
on a.empid=b.empid
group by a.empid
/*
empid classid name cname
A01 C01 tom english --为什么要消除math
A02 C03 mary physics
A03 gary
SQL 语句如何写*/
drop table a,b
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,isnull(b.classid,'') classid,a.name,isnull(b.cname,'') cname
from @t1 a
left outer join
@t2 b
on a.empid=b.empid
and not exists (select 1 from @t2 where empid=b.empid and classid<b.classid)
from t1 a
inner join
t2 b
on a.empid=b.empid
(方法1)select A表.empid,B表.classid, A表.name,B表.cname from A表,B表 where A表.empid=B表.empid
(方法2)select A表.empid,B表.classid,A表 .name,B表.cname from A表 inner join B表 on A表.empid=B表.empid
from t1 a
inner join
t2 b
on a.empid=b.empid
from (select top 1 classid,cname order by ClassID) b
where a.empid=b.empid
from (select top 1 empid,classid,cname order by ClassID) b
where a.empid=b.empid
select T.empid,T.name,T.classid,T.cname from
(
select row_number() over (partition by A.empid order by A.empid) as sel,
A.empid,A.name,B.classid,B.cname
from A left outer join B on A.empid = B.empid order by A.EMPID,B.classid
) T where T.sel = 1
;
http://code.kingofcoders.com10TB代码Search engine
http://search.kingofcoders.com