首先，我搜了一個歷史帖子，情況不太一樣：https://bbs.csdn.net/topics/392478264
我的SQL，包含了5個子查詢。TotalAmount、TotalPaid會導致排序錯誤，PenaltyInterest、PenaltyPaid、PenaltyChange不會導致。
確認排序欄位沒有重復，使用Id也是同樣會亂序。
SQL如下：
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY bill.AddTime DESC) rn,
bill.AddTime AS BillAddTime,
t.*,
(SELECT ISNULL(SUM(pd.Amount), 0)
FROM Tengen_PenaltyDetails pd
WHERE pd.Marks = 0
AND pd.BillId = bill.Id
AND pd.Category = 0) AS PenaltyInterest,
(SELECT ISNULL(SUM(pd.Amount), 0)
FROM Tengen_PenaltyDetails pd
WHERE pd.Marks = 0
AND pd.BillId = bill.Id
AND pd.Category = 1) AS PenaltyPaid,
(SELECT ISNULL(SUM(pd.Amount), 0)
FROM Tengen_PenaltyDetails pd
WHERE pd.Marks = 0
AND pd.BillId = bill.Id
AND pd.Category = 2) AS PenaltyChange,
(SELECT SUM(rpd.TotalAmount)
FROM Tengen_RepaymentPlanDetails rpd
WHERE rpd.Marks = 0
AND rpd.PlanId = rp.Id) AS TotalAmount,
(SELECT ISNULL(SUM(rr.Amount), 0)
FROM Tengen_RepaymentRecords rr
WHERE rr.Marks = 0
AND rr.BillId = bill.Id) AS TotalPaid
FROM Company_Bills t
LEFT JOIN Company_Corps corp ON corp.Id = t.CorpId
LEFT JOIN Tengen_Bills bill ON bill.Id = t.BillId
LEFT JOIN Tenant_Customers cus ON cus.Id = bill.CustomerId
LEFT JOIN Tengen_RepaymentPlans rp ON rp.BillId = bill.Id
WHERE t.Marks = 0
AND t.AuditStatus = 1
AND bill.Marks = 0
AND bill.AuditStatus = 1
AND t.CompanyId = 2
AND t.AuditStatus = 1
AND t.LoanDate IS NOT NULL) paged
WHERE rn >= 1
AND rn <= 10
圖1是包含TotalAmount、TotalPaid的結果
圖2是不包含TotalAmount、TotalPaid的結果
請問是什么原因導致的？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/234632.html
標籤：疑難問題
上一篇：求大神幫忙