我需要按 File_Date 分解這兩個子查詢。現在它只是給了我每個 Service_Code 的記錄總數。
SELECT
[Request File].File_Date,
Count([Request File].SSN) AS [Borrower Count],
Sum([Request File].Discharge_Amt) AS Total_Discharge_Amt,
(SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="500") AS 500_Count,
(SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="579") AS 579_Count
FROM [Request File]
GROUP BY [Request File].File_Date;
如果我嘗試對兩個子查詢按 File_Date 分組，則會收到錯誤訊息“此子查詢最多可以回傳一條記錄”。
SELECT
[Request File].File_Date,
Count([Request File].SSN) AS [Borrower Count],
Sum([Request File].Discharge_Amt) AS Total_Discharge_Amt,
(SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="500" GROUP BY [Request File].File_Date) AS 500_Count,
(SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="579" GROUP BY [Request File].File_Date) AS 579_Count
FROM [Request File]
GROUP BY [Request File].File_Date;
uj5u.com熱心網友回復：
在 SELECT-list 中，子查詢必須回傳一列和一條記錄，因為該值將插入到一個結果欄位中。
由于所有資料都來自同一張表，因此不需要子查詢。取而代之的是，我們使用該技巧通過1在滿足條件時和0其他情況下求和來有條件地計算記錄：
SELECT
File_Date,
Count(SSN) AS [Borrower Count],
Sum(Discharge_Amt) AS Total_Discharge_Amt,
Sum(IIf(Servicer_Code="500", 1, 0)) AS 500_Count,
Sum(IIf(Servicer_Code="579", 1, 0)) AS 579_Count
FROM [Request File]
GROUP BY File_Date;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/522095.html
上一篇：如何將復選框重置為默認值？