时间戳

首页 > 解决方案 > 返回不重叠的唯一值

sql - 返回不重叠的唯一值

问题描述

我有一个事务数据库，我需要比较两个表的不匹配情况

我没有足够的值来防止结果重叠：

这是我的 SQL 代码

USE DB
SELECT DISTINCT T1.loan_Account, T1.Payment_Received, T1.Pay_Amount AS Pending
    , T2.Pay_Amount AS CheckRegister
FROM [PendingToPay] T1, [CheckRegister] T2
WHERE T1.loan_Account = T2.Loan_Account 
AND T1.Payment_Received = T2.Received
ORDER BY T1.Payment_Received

如何过滤掉这样的结果？

LALUZ   2020-02-20 00:00:00.0000000 25.00   25.00
LALUZ   2020-02-20 00:00:00.0000000 25.00   27.12
LALUZ   2020-02-20 00:00:00.0000000 25.00   1149.98
LALUZ   2020-02-20 00:00:00.0000000 27.12   25.00
LALUZ   2020-02-20 00:00:00.0000000 27.12   27.12
LALUZ   2020-02-20 00:00:00.0000000 27.12   1149.98
LALUZ   2020-02-20 00:00:00.0000000 1149.98 25.00
LALUZ   2020-02-20 00:00:00.0000000 1149.98 27.12
LALUZ   2020-02-20 00:00:00.0000000 1149.98 1149.98

标签： sqlsql-server

解决方案

如果我理解正确，您需要检查两个表中的 pay_amount 是否不相等。

SELECT DISTINCT 
T1.loan_Account,
T1.Payment_Received, 
T1.Pay_Amount as Pending, 
T2.Pay_Amount as CheckRegister
FROM [PendingToPay] T1
JOIN [CheckRegister] T2 ON T1.loan_Account=T2.Loan_Account  
                       AND T1.Payment_Received = T2.Received
WHERE T1.Pay_Amount <> T2.Pay_Amount
ORDER BY T1.Payment_Received

您还可以使用 group by 来汇总每个帐户一天的付款，而不是打印多个结果。当然，如果您只对每天的错配感兴趣，而不是对错配的个别交易感兴趣。

SELECT T1.loan_Account,
  CAST(T1.Payment_Received as DATE) as Payment_Received, 
   SUM(T1.Pay_Amount) as Pending, 
   SUM(T2.Pay_Amount) as CheckRegister
FROM [PendingToPay] T1
JOIN [CheckRegister] T2 ON T1.loan_Account=T2.Loan_Account  
                       AND T1.Payment_Received = T2.Received
GROUP BY T1.loan_Account,CAST(T1.Payment_Received as DATE)
HAVING SUM(T1.Pay_Amount) <> SUM(T2.Pay_Amount)
ORDER BY T1.Payment_Received

推荐阅读