以右表最后一项为条件的 SQL 连接

Question

抱歉，如果标题不太好理解，我连表达都困难!

为简化起见，我在 SQL Server 2012 数据库中有两个表:一个包含请求，另一个包含与这些请求相关的操作(来自服务管理器软件)

Table REQUESTS            Table ACTIONS
+------+-------+---+      +---------+------+--------+
|Req_ID|RFC_Num|...|      |Action_ID|Req_ID|Group_ID|
+------+-------+---+      +---------+------+--------+
| 1    | I01   |...|      |  a      | 1    | 10     |
| 2    | I02   |...|      |  b      | 1    | 20     |
| 3    | I03   |...|      |  c      | 1    | 38     |
| 4    | I04   |...|      |  d      | 1    | 38     |
+------+-------+---+      |  e      | 2    | 10     |
                          |  f      | 2    | 38     |
                          |  g      | 2    | 20     |
                          |  h      | 3    | 38     |
                          |  i      | 4    | 10     |
                          +---------+------+--------+

我希望能够选择该请求的最后一个操作具有 group_id = 38 的所有请求，因此它应该返回 req_id 为 1 和 3 的请求，而不是 req2，因为最后一个操作是由另一个组而不是 38 执行的也不涉及第 38 组的 req4。

我试过类似的东西

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID) 
        and (select group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            ).GROUP_ID = 38
        )

但我不知道如何放置“max(action_id)”(或按 desc 顺序选择前 1 个)，而且我真的不明白如何使用诸如 having 之类的东西。

我也尝试过这样的请求:

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID) 
        and (select top 1 a2.action_id, a2.group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            group by a2.ACTION_ID
            order by a2.action_id desc
            ).GROUP_ID = 38
        ) 

但是我得到了这两个错误:

Msg 8120, Level 16, State 1, Line 6
Column ACTION.GROUP_ID is not valid in the selection list because it's not contained in an aggregation function or in the GROUP BY clause.

Msg 116, Level 16, State 1, Line 11
Only one expression can be specified in the selection list when the subquery is not introduced by EXISTS.

谢谢你的帮助!

Answer 1

您的查询看起来比需要的要长。试试这个:

SELECT *
FROM requests r
WHERE (
    SELECT TOP 1 Group_ID
    FROM actions
    WHERE Req_ID = r.Req_ID
    ORDER BY Action_ID DESC
) = 38

SQL Fiddle