SQL Server查询如何避免相同SupplyInvoiceID的TotalBill列重复输出

解决方案 #

你可以通过窗口函数标记同一SupplyInvoiceID下的首行记录，仅在首行返回TotalBill值即可实现需求，修改后的查询语句如下：

SELECT 
   si.SupplyInvoiceID,
   si.CompanyID,
   -- 仅同一发票下的第一条付款记录显示总账单金额，其余行返回空
   CASE 
      WHEN ROW_NUMBER() OVER(PARTITION BY si.SupplyInvoiceID ORDER BY siph.SupplyPaymentID ASC) = 1 
      THEN si.TotalBill 
      ELSE NULL 
   END AS TotalBill,
   siph.BillPaidAmount,
   si.TotalBill - SUM(siph.BillPaidAmount) 
   over( partition by si.SupplyInvoiceID order by siph.SupplyPaymentID  asc) as RemainingBillAmount
from
   SupplyInvoicePaymentHistory siph 
   left join
      SupplyInvoice si 
      on siph.SupplyInvoiceID = si.SupplyInvoiceID 

如果需要数值为空时显示空白而非NULL，可以将TotalBill转为字符串类型后修改ELSE分支返回空字符串：

CASE 
   WHEN ROW_NUMBER() OVER(PARTITION BY si.SupplyInvoiceID ORDER BY siph.SupplyPaymentID ASC) = 1 
   THEN CAST(si.TotalBill AS VARCHAR(50))
   ELSE '' 
END AS TotalBill

内容的提问来源于stack exchange，提问作者shakeel ahmad