在SQL中連接表時如何避免重復

我有下表顯示貸款如何連接到借款人，以及貸款如何連接到借款人質押的房地產抵押品。一些貸款與 2 個借款人有關。我想看看所有連接到 RE 財產的借款人欠了多少錢，而不是重復計算兩個借款人都是義務人的貸款。例如，假設以下表格：

1. 貸款

2. Borrowers_Loans

3. RE_Properties

現在，我想計算與 RE 財產相關的借款人總數，如下所示：

但是，運行下面的雙重計算 Loan_2（并將 Property_2 的總和顯示為 150,000），因為它連接到兩個借款人：

SELECT RE.Property_ID, SUM(L.Total_Claim)
FROM RE_Properties RE
INNER JOIN Borrowers_Loans BL ON BL.Loan_ID = RE.Loan_ID
INNER JOIN Borrowers_Loans BLA ON BLA.Borrower_ID = BL.Borrower_ID
INNER JOIN Loans L ON L.Loan_ID = BLA.Loan_ID
GROUP BY RE.Property_ID

有什么建議？謝謝！

SQL Fiddle

CREATE TABLE RE_Properties (
  Property_ID nvarchar(255),
  Loan_ID nvarchar(255));
  
CREATE TABLE Borrowers_Loans (
  Borrower_ID nvarchar(255),
  Loan_ID nvarchar(255));
  
CREATE TABLE Loans (
  Loan_ID nvarchar(255),
  Total_Claim int);
  
INSERT INTO RE_Properties (Property_ID, Loan_ID) VALUES ('Property_1', 'Loan_1');
INSERT INTO RE_Properties (Property_ID, Loan_ID) VALUES ('Property_2', 'Loan_2');
INSERT INTO RE_Properties (Property_ID, Loan_ID) VALUES ('Property_3', 'Loan_3');

INSERT INTO Borrowers_Loans (Borrower_ID, Loan_ID) VALUES ('Borrower_1', 'Loan_1');
INSERT INTO Borrowers_Loans (Borrower_ID, Loan_ID) VALUES ('Borrower_1', 'Loan_2');
INSERT INTO Borrowers_Loans (Borrower_ID, Loan_ID) VALUES ('Borrower_2', 'Loan_2');
INSERT INTO Borrowers_Loans (Borrower_ID, Loan_ID) VALUES ('Borrower_2', 'Loan_3');

INSERT INTO Loans (Loan_ID, Total_Claim) VALUES ('Loan_1', 50000);
INSERT INTO Loans (Loan_ID, Total_Claim) VALUES ('Loan_2', 30000);
INSERT INTO Loans (Loan_ID, Total_Claim) VALUES ('Loan_3', 40000);

uj5u.com熱心網友回復：

可能有一種更簡單的方法，但是使用您的查詢作為起點，您可以確保每筆貸款只計算一次，方法是按金額分組Loan_ID（MAX或者FIRST沒關系，因為我們知道它的值相同），如下所示：

WITH Cte as (
    SELECT RE.Property_ID
        , L.Loan_ID, MAX(L.Total_Claim) Total_Claim
    FROM RE_Properties RE
    INNER JOIN Borrowers_Loans BL ON BL.Loan_ID = RE.Loan_ID
    INNER JOIN Borrowers_Loans BLA ON BLA.Borrower_ID = BL.Borrower_ID
    INNER JOIN Loans L ON L.Loan_ID = BLA.Loan_ID
    GROUP BY RE.Property_ID, L.Loan_ID
)
SELECT Property_ID, SUM(Total_Claim)
FROM Cte
GROUP BY Property_ID;

uj5u.com熱心網友回復：

每個借款人的風險是與其相關的所有貸款的總和（假設如果任何相關借款人違約，他們會采用全部貸款）

select bl.Borrower_ID, sum(loans.total_claim) borrower_total_risk
from Borrowers_Loans as bl
inner join Loans on bl.loan_id = loans.loan_id

每個財產的風險是該財產的貸款價值

select p.Property_ID, sum(loans.total_claim) property_loan_total
from RE_Properties as p
inner join Loans on p.Loan_ID = loans.Loan_ID
group by p.Property_ID

如果我要把它結合起來，我不會只做一個簡單的求和，例如：

SELECT
      RE.Property_ID
    , max(L.Total_Claim) AS Total_Claim
    , count(distinct bl.Borrower_ID) num_borrowers 
    , min(b_risk.borrower_total_risk) as min_borrower_total_risk
    , max(b_risk.borrower_total_risk) as max_borrower_total_risk
FROM RE_Properties RE
  INNER JOIN Loans L ON L.Loan_ID = RE.Loan_ID
  LEFT JOIN Borrowers_Loans BL ON BL.Loan_ID = RE.Loan_ID
  LEFT JOIN (
              select bl.Borrower_ID, sum(loans.total_claim) borrower_total_risk
              from Borrowers_Loans as bl
              left join Loans on bl.loan_id = loans.loan_id
              group by bl.Borrower_ID
              ) as b_risk on bl.Borrower_ID = b_risk.Borrower_ID
GROUP BY RE.Property_ID;

結果：

| Property_ID | Total_Claim | num_borrowers | min_borrower_total_risk | max_borrower_total_risk |
|-------------|-------------|---------------|-------------------------|-------------------------|
|  Property_1 |       50000 |             1 |                   80000 |                   80000 |
|  Property_2 |       30000 |             2 |                   70000 |                   80000 |
|  Property_3 |       40000 |             1 |                   70000 |                   70000 |

請注意，借款人的數量在需要時顯示為 2，并且不需要多次連接到同一個表。

http://sqlfiddle.com/#!9/9280d4/26/1

uj5u.com熱心網友回復：

似乎沒有必要讓借款人參與進來。只加入房產和貸款

RE_Properties           Loans   
Property_ID Loan_ID     Loan_ID Total_Claim
Property_1  Loan_1      Loan_1  50000
Property_2  Loan_2      Loan_2  30000
Property_3  Loan_3      Loan_3  40000
                        Loan_4  10000
                        Loan_5  70000

標籤：sql sql-server

上一篇：如何在不復制SQL中的值的情況下將兩列的值合并為一列？

下一篇：選擇具有多個地址的客戶