There are two tables, A and B.
Aid scriptid taskid yearID
1 5 3 1
2 5 4 1
3 5 5 1
4 6 3 1
5 6 4 1
6 7 3 2
7 7 5 2
8 8 2 2
9 8 6 2
Table B is as follows:
Bid aid
11 1
12 2
13 3
14 4
15 5
16 6
17 7
18 8
19 9
Obtain the maximum value of yearID = 1, scriptid = 5, and taskid not greater than 4 in table.
Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1 And Scriptid = 5 And Taskid <= 4
Group By Scriptid
Only scriptid and taskid of a can be obtained above. If you want to obtain aid, you can use subquery
Select A1.id, a1.scriptid, a1.taskid
From A A1
Join
( Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1 And Scriptid = 5 And Taskid <= 4
Group By Scriptid
) A2 On A1.scriptid = A2.scriptid And A1.taskid = A2.taskid
If you associate a's records that meet the preceding conditions with table B and obtain the ID of Table B, you can use multi-level subquery:
Select B. * ,. * From B
Join
( Select A1.id, a1.scriptid, a1.taskid
From A A1
Join
( Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1 And Scriptid = 5 And Taskid <= 4
Group By Scriptid
) A2 On A1.scriptid = A2.scriptid And A1.taskid = A2.taskid
) On A. ID = B. Aid