如何在1对多的表中查找最新的数据

[lih163]

表A
aa bb
1 g

表B
aa cc dd
1 2 t
1 3 t
2 1 H

要求结果
1 g 3 T
用一条SQl语句写出来

[hhhdyj]

取cc最大的？
SELECT
FROM 表A A
INNER JOIN (SELECT AA, CC, DD FROM 表B T WHERE NOT EXISTS(SELECT 1 FROM 表B WHERE AA = T.AA AND CC > T.CC)B
ON A.AA = B.AA

[baoshan]

select a.aa,a.bb,max(b.cc),b.dd from 表a a left join 表b b on a.aa=b.aa

[liuyxit]

declare @a table (aa int ,bb char(1))
declare @b table (aa int ,cc int ,dd char(1))


insert into @a
select 1,'g'
union all select 2,'g'

insert into @b
select 1,2,'t'
union all select 1,3,'t'
union all select 2,1,'H'
union all select 2,2,'H'



select * from @a a
join (select aa,cc,dd from @b b1 where b1.cc = (select max(cc) from @b where aa = b1.aa)) as b
on a.aa = b.aa

/*结果
aa bb aa cc dd
----------- ---- ----------- ----------- ----
2 g 2 2 H
1 g 1 3 t
*/

[dawugui]

if object_id('pubs..a') is not null
drop table a
go

create table a
(
aa int,
bb char(1)
)

insert into a(aa,bb) values(1,'g')

if object_id('pubs..b') is not null
drop table b
go

create table b
(
aa int,
cc char(1),
dd char(1)
)

insert into b(aa,cc,dd) values(1,2,'t')
insert into b(aa,cc,dd) values(1,3,'t')
insert into b(aa,cc,dd) values(2,1,'H')

select a.* , m.cc,m.dd from a,
(
select b.* from b,(select aa , max(cc) as cc from b group by aa) t
where b.aa=t.aa and b.cc = t.cc
) m
where a.aa = m.aa

drop table a
drop table b

aa bb cc dd
----------- ---- ---- ----
1 g 3 t

（所影响的行数为 1 行）

[marco08]

create table A(aa int, bb char(1))
insert A select 1, 'g'

create table B(aa int, cc int, dd char(1))
insert B select 1, 2, 't'
union all select 1, 3, 't'
union all select 2, 1, 'H'


select A.aa, A.bb, max(B.cc) as cc, B.dd from A
inner join B on A.aa=B.aa
group by A.aa, A.bb, B.dd


--result
aa bb cc dd
----------- ---- ----------- ----
1 g 3 t

(1 row(s) affected)

[lih163]

up

[xiaoku]

呵呵...
lz 上面不是写出来了吗?