在 SQL Server 2014 中获取 JOIN 上的重复项

Question

My Tables：

账户 A
板 P
FinTransMaster F
拓领交通 T

关系：

A.AccountId = P.AccountId  
A.AccountId = F.AccountId  
A.AccountId = P.AccountId = F.AccountId  
P.LicPlateNo = T.LicPlateNo  
T.AccountId != A.AccountId  
T.AccountId != P.AccountId  
T.AccountId != F.AccountId 

我需要以下所有记录：

     T.AccountId = '123456'   
 AND EXISTS F.TransCode = 'TOLL'   
 AND NOT EXISTS F.TransCode = 'PYMT'
 AND A.CurrentBalance > 0

到目前为止我的代码：

SELECT A.*
FROM Account A
INNER JOIN Plate P ON P.AccountId = A.AccountId
INNER JOIN TollTrans T ON T.LicPlateNo = P.LicPlateNo
WHERE EXISTS     (SELECT 1
                  FROM FinTransMaster F
                  WHERE F.AcctID = A.AccountID AND F.TransCode = 'TOLL'
                  ) 
  AND NOT EXISTS (SELECT 1
                  FROM FinTransMaster F
                  WHERE F.AcctID = A.AccountID AND F.TransCode = 'PYMT'
                  )
  AND T.AccountId = '123456'
  AND A.CurrentBalance > 0
ORDER BY BalanceDT DESC

每个帐户都有一个车牌。某些帐户有多个车牌。也许，这就是我得到重复的原因。

如何优化此代码？

Answer 1

使用

select distinct a.col1,a.col2....
(rest of the query)

另请查看是否可以将 join 替换为 exist，因为您没有从其他表中选择任何内容，因此性能会更好。

Answer 2

您可以使用不同的

SELECT distinct A.*
FROM Account A
INNER JOIN Plate P ON P.AccountId = A.AccountId
INNER JOIN TollTrans T ON T.LicPlateNo = P.LicPlateNo
INNER JOIN FinTransMaster F on F.AcctID = A.AccountID AND F.TransCode = 'TOLL'
WHERE NOT EXISTS (SELECT *
                  FROM FinTransMaster G
                  WHERE G.AcctID = A.AccountID AND G.TransCode = 'PYMT'
                  )
AND T.AccountId = '123456' AND A.CurrentBalance > 0
ORDER BY BalanceDT DESC

Answer 3

如果您只对 Account 表感兴趣，只需将其他表移动到 EXISTS 子查询：

SELECT *
FROM Account A
WHERE EXISTS     (SELECT 1
                  FROM FinTransMaster F
                  WHERE F.AcctID = A.AccountID AND F.TransCode = 'TOLL'
                  ) 
  AND NOT EXISTS (SELECT 1
                  FROM FinTransMaster F
                  WHERE F.AcctID = A.AccountID AND F.TransCode = 'PYMT'
                  )
  AND EXISTS      (SELECT 1
                  FROM Plate P
                  INNER JOIN TollTrans T ON T.LicPlateNo = P.LicPlateNo
                  WHERE P.AccountId = A.AccountId
                    AND T.AccountId = '123456'
                  )
  AND A.CurrentBalance > 0
ORDER BY BalanceDT DESC

如果BalanceDT不在"帐户"表中，则无法执行此操作，但是，您没有限定该列。