求left join

[lichxi1002]

表A
id A1 A2 A3
1 a11 sad sfd
2 2fg 23k 2ed
3 lsd 0j0 lk2

表B
id Aid B1 B2
1 1 0ik -k[
2 1 okd kl3
3 2 kle lwe
4 3 lkd lk3
5 3 lk3 lkd
6 3 lkw lkj


select a.*,b.B1 from A a
left join B as b on a.id = b.Aid

我要得到
id A1 A2 A3 B1
1 a11 sad sfd okd
2 2fg 23k 2ed kle
3 lsd 0j0 lk2 lkw

[-狙击手-]

select a.*,b.B1 from A a 
left join (select aid,max(b1) as b1 from b group by aid)as b 
on a.id = b.Aid

[lichxi1002]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codeselecta.*,b.B1fromA aleftjoin(selectaid,max(b1)asb1frombgroupbyaid)asbona.id=b.Aid
[/Quote]

怎么可以用max(B1)???

[-狙击手-]

试试是不是满足你的结果

------------------------------------
-- 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 

[nzperfect]

--> 测试数据: #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 #B

select 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
*/

[nzperfect]

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

这样行，顶你丫的

[lichxi1002]

[Quote=引用 3 楼 happyflystone 的回复:]
试试是不是满足你的结果

SQL code--------------------------------------Author:Flystone--Version:V1.001--Date:2008-07-29 15:22:43--------------------------------------Test Data: taIfobject_id('ta')isnotnullDroptabletaGoCreatetableta(idint,A1nvarchar(3),A2nvarchar(3),A3nvarchar(3))GoInsertintotaselect1,'a11','sad','sfd'unionallselect2,'2fg','23k','2ed'unionallselect3,'lsd','0j0','lk2'Go--Test Dat…
[/Quote]

max(B1)只是巧合碰上了，原意是要取B表中ID值最大的，B1是字符类型的

[nzperfect]

[Quote=引用 6 楼 happyflystone 的回复:]
恩

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

这样行，顶你丫的
[/Quote]

听说最近流行 bs汝. ...呵呵

[-狙击手-]

最近流行
BS 侬

[-狙击手-]

[Quote=引用 7 楼 lichxi1002 的回复:]
引用 3 楼 happyflystone 的回复:
试试是不是满足你的结果

SQL code--------------------------------------Author:Flystone--Version:V1.001--Date:2008-07-29 15:22:43--------------------------------------Test Data: taIfobject_id('ta')isnotnullDroptabletaGoCreatetableta(idint,A1nvarchar(3),A2nvarchar(3),A3nvarchar(3))GoInsertintotaselect1,'a11','sad','sfd'unionallselect2,'2fg','23k','2ed'unionallsel…
[/Quote]

没考虑你是想要ID最大的那条

[三下鱼]

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 

[三下鱼]

[Quote=引用 11 楼 clq271520093 的回复:]
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
[/Quote]
===
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
看着上边自己友想的，不知道对不对

[-狙击手-]

完美你的这个哈稀表后为了优化重绕操作生成临时表代价太高了

[nzperfect]

[Quote=引用 14 楼 happyflystone 的回复:]
完美你的这个哈稀表后为了优化重绕操作生成临时表代价太高了
[/Quote]

要看数据分布了..偶不是发过测试贴了吗..

[lichxi1002]

这样子B表要查询三次，代价是不是太大？

[-狙击手-]

[Quote=引用 15 楼 perfectaction 的回复:]
引用 14 楼 happyflystone 的回复:
完美你的这个哈稀表后为了优化重绕操作生成临时表代价太高了


要看数据分布了..偶不是发过测试贴了吗..
[/Quote]

俺看过，记得在帖里顺便BS侬了一下，嘿嘿

[nzperfect]

[Quote=引用 17 楼 happyflystone 的回复:]
引用 15 楼 perfectaction 的回复:
引用 14 楼 happyflystone 的回复:
完美你的这个哈稀表后为了优化重绕操作生成临时表代价太高了


要看数据分布了..偶不是发过测试贴了吗..


俺看过，记得在帖里顺便BS侬了一下，嘿嘿
[/Quote]

我说的是这个啊:
http://topic.csdn.net/u/20080729/09/bce94d21-6190-4c68-9d87-5bc523551c5a.html

[中国风]

select
	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)

[lichxi1002]

[Quote=引用 19 楼 roy_88 的回复:]
SQL codeselecta.*,c.B1fromaleftjoinb cona.ID=c.Aidandc.ID=(selectmax(ID)frombwhereAid=c.Aid)
[/Quote]
就选这个了

谢谢各位的回复