试试是不是满足你的结果------------------------------------ -- Author:Flystone -- Version:V1.001 -- Date:2008-07-29 15:22:43 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(id int,A1 nvarchar(3),A2 nvarchar(3),A3 nvarchar(3)) Go Insert into ta select 1,'a11','sad','sfd' union all select 2,'2fg','23k','2ed' union all select 3,'lsd','0j0','lk2' Go -- Test Data: tB If object_id('tB') is not null Drop table tB Go Create table tB(id int,Aid int,B1 nvarchar(3),B2 nvarchar(3)) Go Insert into tB select 1,1,'0ik','-k[' union all select 2,1,'okd','kl3' union all select 3,2,'kle','lwe' union all select 4,3,'lkd','lk3' union all select 5,3,'lk3','lkd' union all select 6,3,'lkw','lkj' Go --Start select a.*,b.B1 from ta a left join (select aid,max(b1) as b1 from tb group by aid)as b on a.id = b.Aid --Result: /*id A1 A2 A3 B1 ----------- ---- ---- ---- ---- 1 a11 sad sfd okd 2 2fg 23k 2ed kle 3 lsd 0j0 lk2 lkw*/ --End
--> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A (id int,A1 varchar(3),A2 varchar(3),A3 varchar(3)) insert into #A select 1,'a11','sad','sfd' union all select 2,'2fg','23k','2ed' union all select 3,'lsd','0j0','lk2'if object_id('tempdb.dbo.#B') is not null drop table #B go create table #B (id int,Aid int,B1 varchar(3),B2 varchar(3)) insert into #B select 1,1,'0ik','-k[' union all select 2,1,'okd','kl3' union all select 3,2,'kle','lwe' union all select 4,3,'lkd','lk3' union all select 5,3,'lk3','lkd' union all select 6,3,'lkw','lkj'select * from #A select * from #Bselect a.*,b.B1 from #a as a left join ( select a.* from #b as a inner join (select max(id) as id from #b group by aid) as b on a.id=b.id ) as b on a.id=b.aid/* 1 a11 sad sfd okd 2 2fg 23k 2ed kle 3 lsd 0j0 lk2 lkw */
max(b1)是当前所举数据的一个巧合。
恩 select a.* from #b as a inner join (select max(id) as id from #b group by aid) as b on a.id=b.id这样行,顶你丫的
max(B1)只是巧合碰上了,原意是要取B表中ID值最大的,B1是字符类型的
select a.* from #b as a left outer join (select max(id) as id from #b group by aid) as b on a.id=b.id
哈哈,来个完美BS的语句:------------------------------------ -- Author:Flystone -- Version:V1.001 -- Date:2008-07-29 15:22:43 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(id int,A1 nvarchar(3),A2 nvarchar(3),A3 nvarchar(3)) Go Insert into ta select 1,'a11','sad','sfd' union all select 2,'2fg','23k','2ed' union all select 3,'lsd','0j0','lk2' Go -- Test Data: tB If object_id('tB') is not null Drop table tB Go Create table tB(id int,Aid int,B1 nvarchar(3),B2 nvarchar(3)) Go Insert into tB select 1,1,'0ik','-k[' union all select 2,1,'okd','kl3' union all select 3,2,'kle','lwe' union all select 4,3,'lkd','lk3' union all select 5,3,'lk3','lkd' union all select 6,3,'lkw','lkj' Go --Start select a.*,b.B1 from ta a left join (select * from tb c where not exists(select 1 from tb where aid = c.aid and id > c.id))as b on a.id = b.Aid --Result: /*id A1 A2 A3 B1 ----------- ---- ---- ---- ---- 1 a11 sad sfd okd 2 2fg 23k 2ed kle 3 lsd 0j0 lk2 lkw*/ --End
=== select a.* from #b as a left outer join (select max(id) as id from #b group by aid) as b on a.id=b.aid 看着上边自己友想的,不知道对不对
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-29 15:22:43
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,A1 nvarchar(3),A2 nvarchar(3),A3 nvarchar(3))
Go
Insert into ta
select 1,'a11','sad','sfd' union all
select 2,'2fg','23k','2ed' union all
select 3,'lsd','0j0','lk2'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(id int,Aid int,B1 nvarchar(3),B2 nvarchar(3))
Go
Insert into tB
select 1,1,'0ik','-k[' union all
select 2,1,'okd','kl3' union all
select 3,2,'kle','lwe' union all
select 4,3,'lkd','lk3' union all
select 5,3,'lk3','lkd' union all
select 6,3,'lkw','lkj'
Go
--Start
select a.*,b.B1 from ta a
left join (select aid,max(b1) as b1 from tb group by aid)as b
on a.id = b.Aid
--Result:
/*id A1 A2 A3 B1
----------- ---- ---- ---- ----
1 a11 sad sfd okd
2 2fg 23k 2ed kle
3 lsd 0j0 lk2 lkw*/
--End
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (id int,A1 varchar(3),A2 varchar(3),A3 varchar(3))
insert into #A
select 1,'a11','sad','sfd' union all
select 2,'2fg','23k','2ed' union all
select 3,'lsd','0j0','lk2'if object_id('tempdb.dbo.#B') is not null drop table #B
go
create table #B (id int,Aid int,B1 varchar(3),B2 varchar(3))
insert into #B
select 1,1,'0ik','-k[' union all
select 2,1,'okd','kl3' union all
select 3,2,'kle','lwe' union all
select 4,3,'lkd','lk3' union all
select 5,3,'lk3','lkd' union all
select 6,3,'lkw','lkj'select * from #A
select * from #Bselect a.*,b.B1 from #a as a
left join
(
select a.* from #b as a
inner join (select max(id) as id from #b group by aid) as b
on a.id=b.id
) as b on a.id=b.aid/*
1 a11 sad sfd okd
2 2fg 23k 2ed kle
3 lsd 0j0 lk2 lkw
*/
inner join (select max(id) as id from #b group by aid) as b
on a.id=b.id这样行,顶你丫的
max(B1)只是巧合碰上了,原意是要取B表中ID值最大的,B1是字符类型的
left outer join (select max(id) as id from #b group by aid) as b
on a.id=b.id
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-29 15:22:43
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,A1 nvarchar(3),A2 nvarchar(3),A3 nvarchar(3))
Go
Insert into ta
select 1,'a11','sad','sfd' union all
select 2,'2fg','23k','2ed' union all
select 3,'lsd','0j0','lk2'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(id int,Aid int,B1 nvarchar(3),B2 nvarchar(3))
Go
Insert into tB
select 1,1,'0ik','-k[' union all
select 2,1,'okd','kl3' union all
select 3,2,'kle','lwe' union all
select 4,3,'lkd','lk3' union all
select 5,3,'lk3','lkd' union all
select 6,3,'lkw','lkj'
Go
--Start
select a.*,b.B1 from ta a
left join (select * from tb c where not exists(select 1 from tb where aid = c.aid and id > c.id))as b
on a.id = b.Aid
--Result:
/*id A1 A2 A3 B1
----------- ---- ---- ---- ----
1 a11 sad sfd okd
2 2fg 23k 2ed kle
3 lsd 0j0 lk2 lkw*/
--End
select a.* from #b as a
left outer join (select max(id) as id from #b group by aid) as b
on a.id=b.aid
看着上边自己友想的,不知道对不对
我说的是这个啊:
http://topic.csdn.net/u/20080729/09/bce94d21-6190-4c68-9d87-5bc523551c5a.html
a.*,c.B1
from
a
left join
b c on a.ID=c.Aid and c.ID=(select max(ID) from b where Aid=c.Aid)