首頁猿問兩張表一對多的連接,取多記錄表中最...

兩張表一對多的連接,取多記錄表中最新的一條數(shù)據(jù)

SQL Server

陪伴而非守候 2018-12-06 15:47:36

A表為主表(問題表),B表示關(guān)聯(lián)表(答案表),A表中一個(gè)問題對應(yīng)B表中多個(gè)回答,如A Ba1,... b1,a1,Time b2,a1,Timeselect * from A left join B on A.a1=B.a1查出來肯定是兩個(gè)記錄,怎么編寫sql查詢A,關(guān)聯(lián)上B表中最新的一條記錄.

查看完整描述

8 回答

千萬里不及你

TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超9個(gè)贊

left join

反對回復(fù) 2019-01-07

守著一只汪

TA貢獻(xiàn)1872條經(jīng)驗(yàn) 獲得超4個(gè)贊

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

反對回復(fù) 2019-01-07

繁星點(diǎn)點(diǎn)滴滴

TA貢獻(xiàn)1803條經(jīng)驗(yàn) 獲得超3個(gè)贊

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

反對回復(fù) 2019-01-07

蠱毒傳說

TA貢獻(xiàn)1895條經(jīng)驗(yàn) 獲得超3個(gè)贊

先連接按照時(shí)間進(jìn)行排序，每次都選擇第一條（top(1)）!

反對回復(fù) 2019-01-07

叮當(dāng)貓咪

TA貢獻(xiàn)1776條經(jīng)驗(yàn) 獲得超12個(gè)贊

分組在連接查詢。

反對回復(fù) 2019-01-07

HUWWW

TA貢獻(xiàn)1874條經(jīng)驗(yàn) 獲得超12個(gè)贊

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

反對回復(fù) 2019-01-07

滄海一幻覺

TA貢獻(xiàn)1824條經(jīng)驗(yàn) 獲得超5個(gè)贊

可以使用with as

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

反對回復(fù) 2019-01-07