如何查找Server 2014中没有值的记录？

Question

我有一个表'FinTrans‘，它的数据如下所示：

---------------------------------
|AcctID     |TransCode |TransAmt|
---------------------------------
|1234567    |TOLL      |    4:30|
---------------------------------
|1234567    |PYMT      |    5:30|
---------------------------------
|1234567    |RFND      |    1:00|
---------------------------------
|2345678    |TOLL      |    4:30|
---------------------------------
|2345678    |TOLL      |    4:30|
---------------------------------
|2345678    |RFND      |    4:30|
---------------------------------
|3456789    |TOLL      |    4:30|
---------------------------------

我有一个“帐户”表，它给出了每个AcctID的帐号。

我需要找到没有'PYMT‘作为TransCode的帐号。

我已经尝试了下面的代码，但很明显，这是行不通的。

SELECT DISTINCT(A.AccountNumber)      
      ,TransCode
      ,SUM(TransAmt)

  FROM FinTrans F
  INNER JOIN Account A ON F.AcctID = A.AccountID

  WHERE TransCode = 'TOLL'
    AND TransCode != 'PYMT'

  GROUP BY A.AccountNumber, TransCode

Answer 1

您可以使用group by和having来完成这一任务。

SELECT A.AccountNumber
FROM FinTrans F INNER JOIN
     Account A
     ON F.AcctID = A.AccountID
WHERE TransCode IN ('PYMT', 'TOLL')
GROUP BY A.AccountNumber
HAVING SUM(CASE WHEN TransCode = 'PYMT' THEN 1 ELSE 0 END) = 0;

您真的需要示例SQL中的其他列吗？它们与你的问题无关。

您也可以使用EXISTS/NOT EXISTS完成这一任务。

select a.*
from account a
where exists (select 1
              from FinTrans f
              where f.AcctID = a.AccountID and f.TransCode = 'TOLL'
             ) and
      not exists (select 1
                  from FinTrans f
                  where f.AcctID = a.AccountID and f.TransCode = 'PYMT'
                 );

这具有不需要聚合的性能优势。

Answer 2

我需要找到没有“PYMT”作为TransCode的帐号

你可以试试这个。用组

SELECT A.AccountNumber
      ,TransCode
      ,SUM(TransAmt)

  FROM FinTrans F
  INNER JOIN Account A ON F.AcctID = A.AccountID
  where TransCode IN ('PYMT', 'TOLL')
  GROUP BY A.AccountNumber, TransCode
  HAVING SUM(IIF(TransCode = 'PYMT',1,0)) = 0

Answer 3

我认为这是做NOT HAVING的正常方式

SELECT AccountNumber
FROM Account AS a
WHERE NOT EXISTS (
      SELECT NULL FROM FinTrans AS f
      WHERE a.AccountID = f.AcctID AND
            f.TransCode = "PYMT")

试着解释你的例子，我相信你是在寻找

SELECT a.AccountNumber, f.TransCode, SUM(f.TransAmt)
FROM FinTrans AS f
INNER JOIN Account AS a ON f.AcctID = a.AccountID
WHERE f.AcctID IN (SELECT f.AcctID FROM FinTrans AS F GROUP BY f.AcctID 
                   HAVING SUM(CASE WHEN f.TransCode = 'PYMT' THEN 1 ELSE 0 END) = 0)
      AND f.TransCode = 'TOLL'
GROUP BY f.AcctID

使用第一种过滤组的方法的另一个选项是

SELECT a.AccountNumber, f.TransCode, SUM(f.TransAmt)
FROM FinTrans AS f
INNER JOIN Account AS a ON f.AcctID = a.AccountID
WHERE f.AcctID IN (SELECT AcctID FROM FinTrans AS f 
                   WHERE NOT EXISTS (SELECT NULL FROM FinTrans AS f2
                                     WHERE f.AcctID = f2.AcctID AND f2.TransCode = "PYMT"))
                         AND f.TransCode = 'TOLL'
GROUP BY f.AcctID;