CREATE TABLE T_A (ID int, Date int, Value_A int) 
The 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) 

The SELECT, Anderson D a. d. ate, Dr. Alue_A, c. alue_B FROM T_A a 
INNER JOIN (SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY Date ASC) AS the RowId FROM T_B) b ON Anderson, D=b.I D AND a. d. ate=b.D ate 
INNER JOIN (SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY Date ASC) AS the RowId FROM T_B) c ON b.I D=c.I D AND b.R owId=c.R owId + 1 

the purpose is to want to according to the ID in A table, the Date, from B to select the same ID in the table, less than A table Date Date corresponding to the largest Value_B, ask for any efficient concise grammar

CodePudding user response:

Myself to statements have A very serious problem, in the B table can't find A same date in the table is screened out, and the actual demand in most of them are found

CodePudding user response:

 

SELECT A. * FROM T_B A 
The OUTER APPLY (SELECT * FROM TOP 1 T_A WHERE Anderson, D=ID ORDER BY DATE DESC) AS B 
WHERE a. d. ate

CodePudding user response:

 SELECT * 
The FROM t_a A 
OUTER APPLY (SELECT TOP (1) b.V alue_B 
The FROM t_b b 
WHERE Anderson, d=b.i d AND a. [Date] & gt; B. [Date] 
The ORDER BY b. [Date] DESC) b; 

CodePudding user response:

# 3 writing is right, but in the case of large amount of data, or to build a new index to ensure efficiency,