CREATE TABLE T_A(ID int, Date int, Value_A int)目的是想根據A表中的ID,Date，從B表中選出ID相同，小于A表Date的最大日期對應的Value_B， 問下有沒有什么高效簡潔的語法
CREATE TABLE T_B(ID int, Date int, Value_B int)
INSERT INTO T_A
VALUES (1, 20200101, 1),
(1, 20200102, 2),
(1, 20200103, 5),
(2, 20200101, 5),
(2, 20200102, 3),
(2, 20200103, 4),
(3, 20200101, 1),
(3, 20200102, 2),
(3, 20200103, 3)
INSERT INTO T_B
VALUES (1, 20200101, 2),
(1, 20200102, 5),
(1, 20200103, 6),
(2, 20200101, 2),
(2, 20200102, 3),
(2, 20200103, 4),
(3, 20200101, 5),
(3, 20200102, 6),
(3, 20200103, 7)
SELECT a.ID, a.Date, a.Value_A, c.Value_B FROM T_A a
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date ASC) AS RowId FROM T_B ) b ON a.ID = b.ID AND a.Date = b.Date
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date ASC) AS RowId FROM T_B ) c ON b.ID = c.ID AND b.RowId = c.RowId+1
uj5u.com熱心網友回復：
我自己給的陳述句有很嚴重的問題，就在B表中找不到A表中相同的日期就篩選不出來，而實際需求中大部分都是找不到的uj5u.com熱心網友回復：
SELECT A.* FROM T_B A
OUTER APPLY (SELECT TOP 1 * FROM T_A WHERE A.ID=ID ORDER BY DATE DESC) AS B
WHERE A.Date<B.Date
uj5u.com熱心網友回復：
SELECT *
FROM t_a A
OUTER APPLY(SELECT TOP(1) b.Value_B
FROM t_b b
WHERE A.id = b.id AND A.[Date] > b.[Date]
ORDER BY b.[Date] DESC)B;
uj5u.com熱心網友回復：
#3 的寫法是對的， 不過在資料量大的情況下， 還是要建立索引來保證效率。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/41513.html
標籤：疑難問題
上一篇：sql server 自學筆記
下一篇：#c++求大神們解答一下