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
B 表如下
BID AID
11 1
12 2
13 3
14 4
15 5
16 6
17 7
18 8
19 9
获取 A 表中 YearID=1,ScriptID=5, TaskID 为不大于 4 的最大值
select
ScriptID,
max
(TaskID)
as
TaskID
from
A
where YBID = 1 and ScriptID = 5 and TaskID <= 4
group by ScriptID
where YBID = 1 and ScriptID = 5 and TaskID <= 4
group by ScriptID
上面只能获取 A 的 ScriptID 和 TaskID ,如果还要获取 AID, 可以用子查询
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
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
如果将 A 满足上面条件的记录和 B 表关联,获取 B 的ID, 可以用多级子查询:
select
B.
*
,A.
*
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
) A on A.ID = B.AID
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
) A on A.ID = B.AID