# 两张表一对多的连接,取多记录表中最新的一条数据

0

A表为主表(问题表),B表示关联表(答案表),A表中一个问题对应B表中多个回答,如
A B
a1,... b1,a1,Time
b2,a1,Time
select * from A left join B on A.a1=B.a1

Seven_boy | 初学一级 | 园豆：50

0

select  top 1 test1.id,test1.qs,test2.id1,test2.time  from  test1
inner join  test2  on  test1.id=test2.id1

group by test1.id,test1.qs,test2.time,test2.id1

order by  time desc

AStronghcm | 菜鸟二级 |园豆：313 | 2013-05-24 15:55

0

left join

0

select * from A inner join (select table1.* from b table1 inner jioin b table2 on table1.a1 = table2.a1 and table1.Time>table2.Time) tableB inner join on A.a1=tableB.a1

jingjunfeng | 园豆：873 (小虾三级) | 2013-05-24 16:03
0

select * from A as a left join (select * from B where B.Time in (select MAX(B.Time) as Time from B group by B.a1)) as b on a.a1=b.a1

zoom567 | 园豆：212 (菜鸟二级) | 2013-05-24 16:56
0

0

0
SELECT  A.*, B2.*　FROM  A
CROSS APPLY
(
SELECT  TOP 1 B.*
FROM    B
WHERE   B.a1 = A.a1
Order by B.Time Desc ) B2

gunsmoke | 园豆：3592 (老鸟四级) | 2013-05-27 05:14
0

WITH temp AS
(
SELECT MAX(UpdateTIME) AS updatetime,a1 FROM  dbo.tableb  GROUP BY Question
)
SELECT * FROM dbo.tablea
INNER JOIN temp ON dbo.tablea.a1 = temp.a1

mifeng2012 | 园豆：269 (菜鸟二级) | 2013-07-30 14:14

您需要登录以后才能回答，未注册用户请先注册