如果满足条件，SQL返回特定类型的最新记录

Question

希望我不会把事情搞复杂。我编写了以下SQL，返回用户他们最近的事务满足一个条件(TRANS_TYPE NOT IN(4,6,21,23）或DESTORIT_OPTION&64<>64)。

SELECT 
TERMINAL_ID,REGISTER_ID,[USER_ID],sub.CREATE_DATE,sub.TRANS_TYPE_ID,BUS_DATE_ID
FROM (
SELECT
T.TERMINAL_ID
,US.REGISTER_ID 
,U.[USER_ID] 
,T.CREATE_DATE
,T.TRANS_TYPE_ID
,T.BUS_DATE_ID
,T.TRANS_CONFIG_ID
,TT.TRANS_TYPE
, row_number() 
over (partition by U.[USER_ID] 
order by T.CREATE_DATE desc) rn
From [RCMDYNAMIC].[dbo].[Transaction] T
INNER JOIN [RCMDYNAMIC].[dbo].[UserSession] US ON T.USER_SESSION_ID = US.USER_SESSION_ID
INNER JOIN [RCMSTATIC].[dbo].[User] U ON U.[USER_ID] = US.[USER_ID]
INNER JOIN [RCMSTATIC].[dbo].[TransactionType] TT ON T.TRANS_TYPE_ID = TT.TRANS_TYPE_ID
INNER JOIN [RCMSTATIC].[dbo].[Register] R ON US.REGISTER_ID = R.REGISTER_ID
) sub
LEFT JOIN
[RCMSTATIC].[dbo].[DepositConfig] DC
ON sub.TRANS_CONFIG_ID = DC.DEPOSIT_ID
WHERE sub.rn = 1 AND (TRANS_TYPE NOT IN (4, 6, 21, 23) OR DEPOSIT_OPTION & 64 <> 64)

我通过使用

ROW_NUMBER()（分区按U.[USER_ID]顺序按T.CREATE_DATE desc)rn

前面代码中的sub查询将返回所有用户的所有事务，并按DESC顺序对其进行排序，外部SELECT将通过检查rank 1事务来显示满足条件的用户。

我想要的是像这样的东西

FOREACH user如果用户rank 1事务满足条件，则查找用户最近的TRANS_TYPE事务10

User_ID   TRANS_TYPE  DEPOSIT_OPTION Rank
   1           4           7          1
   1           10          7          2
   2           22          64         1
   2           23          4          2
   2           10          126        3
   2            4          7          4
   3           10          3          1
   4           6           64         1 -- doesn't meet the condition 
   4           10          7          2 

WHERE sub.rn = 1 AND (TRANS_TYPE NOT IN (4, 6, 21, 23) OR DEPOSIT_OPTION & 64 <> 64) 

 User_ID   TRANS_TYPE   Rank
   1           10        2
   2           10        3
   3           10        1

对不起，如果问题不是很清楚，我已经尽力了！

Answer 1

drop table if exists dbo.Deposits;

create table dbo.Deposits (
User_ID int
, Trans_Type int
, Deposit_Option int
, Rank int
);

insert into dbo.Deposits (User_ID, Trans_Type, Deposit_Option, Rank)
values (1, 4, 7, 1), (1, 10, 7, 2)
, (2, 22, 64, 1), (2, 23, 4, 2), (2, 10, 126, 3), (2, 4, 7, 4)
, (3, 10, 3, 1)
, (4, 6, 64, 1), (4, 10, 7, 2);

select
sub2.User_ID, sub2.Trans_Type, sub2.Rank
from dbo.Deposits sub
inner join dbo.Deposits sub2 on sub.User_ID = sub2.User_ID
        and sub2.Trans_Type = 10
where sub.Rank = 1 AND (sub.Trans_Type NOT IN (4, 6, 21, 23) OR sub.Deposit_Option & 64 <> 64)