MSSQL条件联接条件

Question

我在加入时遇到了一点麻烦，希望社区能帮上忙。我正在尝试连接transtypeID列上的两个表TRANSTYPES和TRANSTYPES。很简单。但是事务可以是国外的，也可以是国内的，如foreign_account列所示。事务类型可以是国外的，也可以是国内的，并且在TRANSTYPES表中可以有多行。

我正在寻找一个连接，试图匹配ID上的事务和传输类型，以及可能的情况下的foreign_account=foreign_account (x=x或null=null)。如果在foreign_account上没有匹配项，那么它接受具有匹配transtypeID的行，而不关注foreign_account列。

表设置：

CREATE TABLE ##TRANSTYPES
        (transtypeID int,
        tt_name VARCHAR(50),
        foreign_account VARCHAR(5),
        additional_info VARCHAR(20))

INSERT INTO ##TRANSTYPES
    VALUES
    (1000,NULL,102.00),
    (1002, NULL, 103.00),
    (1002, 'x', 104.00),
    (1003, 'x', 105.00),
    (1003, 'x', 106.00), 
    (1003, NULL, 107.00),
    (1003, NULL, 108.00)
CREATE TABLE ##TRANSACTIONS
        (transtypeID int,
        foreign_account VARCHAR(5),
        balance DECIMAL(18,4))

INSERT INTO ##TRANSACTIONS
    VALUES  
        (1000,NULL,102.00 ),
        (1002, NULL, 103.00),
        (1002, 'x', 104.00),
        (1003, 'x', 105.00)

我从一个简单的连接开始，但是我没有得到类型为1003的事务。

SELECT *
FROM ##transtypes TT
FULL outer JOIN ##transactions TRN
    ON tt.transtypeid = trn.transtypeID 
WHERE
    (TRN.foreign_account = TT.foreign_account
    OR (TRN.foreign_account IS NULL AND tt.foreign_account IS NULL))

我有一种感觉，交叉应用是需要的，但我以前从未写过。我正在拼命思考如何对子查询进行正确的处理。我试图在按foreign_account列排序的TRANSTYPE表中选择top 1，但到目前为止还没有成功。

提前谢谢你。

Answer 1

从转换类型ID上的连接开始。然后对您的行进行排名，并只保留更好的匹配(即相同的foreign_account，如果可用)：

SELECT tt_name, additional_info, transtypeID, foreign_account, balance
FROM
(
  SELECT 
    tt.tt_name,
    tt.additional_info, 
    trn.transtypeID,
    trn.foreign_account,
    trn.balance,
    row_number() 
      over (partition by tt.transtypeID, tt.tt_name 
            order by case when (trn.foreign_account = tt.foreign_account)
                            or (trn.foreign_account is null and tt.foreign_account is null)
                               then 1
                               else 2
                     end) as rn
  FROM ##transtypes tt 
  JOIN ##transactions trn ON trn.transtypeID = tt.transtypeID
) ranked
WHERE rn = 1;

Answer 2

您可以使用UNION ALL

SELECT  tt.tt_name,
    tt.additional_info, 
    trn.transtypeID,
    trn.foreign_account,
    trn.balance
FROM ##TRANSTYPES tt INNER JOIN ##TRANSACTIONS trn ON tt.foreign_account = trn.foreign_account

UNION ALL

SELECT  tt.tt_name,
    tt.additional_info, 
    trn.transtypeID,
    trn.foreign_account,
    trn.balance
FROM ##TRANSTYPES tt INNER JOIN ##TRANSACTIONS trn ON tt.transTypeID = trn.transTypeID
WHERE tt.foreign_account <> trn.foreign_account
OR (TRN.foreign_account IS NULL AND tt.foreign_account IS NULL)