如何使這個查詢更簡單

 SELECT IH.InvoiceNo,
   C.Name AS NameofCustomer,
   IP.Amount AS CashAmount,
   0 CreditNote,
   0 ChequeAmount,
   0 Credit,
   IP.Amount AS SalesValue 
 FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=1 AND IP.InvoiceDate 
BETWEEN'2018/05/01' AND '2018/06/19' AND IH.Deleted = 0 
 UNION
SELECT  IH.InvoiceNo,
    C.Name AS NameofCustomer,
    0 CashAmount,
    IP.Amount AS CreditNote,
    0 ChequeAmount,
    0 Credit,
    IP.Amount AS SalesValue 
FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=2 AND IP.InvoiceDate BETWEE'2018/05/01' 
AND '2018/06/19' AND IH.Deleted = 0 
UNION
   SELECT  IH.InvoiceNo,
    C.Name AS NameofCustomer,
    0 CashAmount,
    0 CreditNote,
    IP.Amount AS ChequeAmount,
    0 Credit,
    IP.Amount AS SalesValue 
  FROM InvoiceHeader IH
  LEFT JOIN Customer C ON IH.CustomerId = C.Id
  LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
  LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
  LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
  WHERE DC.Id = 1 AND IP.PaymentTypeId=3 AND IP.InvoiceDate BETWEEN 
 '2018/05/01' AND '2018/06/19' AND IH.Deleted = 0 
  UNION
SELECT  IH.InvoiceNo,
    C.Name AS NameofCustomer,
    0 CashAmount,
    0 CreditNote,
    0 ChequeAmount,
    IP.Amount AS Credit,
    IP.Amount AS SalesValue 
FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=4 AND IP.InvoiceDate BETWEEN 
'2018/05/01' AND '2018/06/19' AND IH.Deleted = 0 
ORDER BY IH.InvoiceNo 

Amount 具有支付的值，PaymentTypeId 具有支付類型。我正在嘗試將數據檢索到 4 個列中，條件為 PaymentTypeId = 1、2、3 或 4 這是我得到的查詢。這個任務有什麼簡單的方法嗎？

好吧，至少看起來你可以擺脫UNIONs. 使用 aCASE ... END獲取cashamount、creditnote和取決於chequeamount的值。在子句中使用（或多個）過濾 for 。credit``paymenttypeid``IN``OR``paymenttypeid``WHERE

SELECT DISTINCT
      ih.invoiceno,
      c.name nameofcustomer,
      CASE ip.pamenttypeid
        WHEN 1
          THEN ip.amount
        ELSE
          0
      END cashamount,
      CASE ip.paymenttypeid
        WHEN 2
          THEN ip.amount
        ELSE
          0
      END creditnote,
      CASE ip.paymenttypeid
        WHEN 3
          THEN ip.amount
        ELSE
          0
      END chequeamount,
      CASE ip.paymenttypeid
        WHEN 4
          THEN ip.amount
        ELSE
          0
      END credit,
      ip.amount salesvalue 
      FROM invoiceheader ih
           LEFT JOIN customer c
                     ON c.id = ih.customerid
           LEFT JOIN invoicedetail id
                     ON id.invoiceno = ih.invoiceno
           LEFT JOIN distributioncenter dc
                     ON dc.id = id.distributioncenterid
           LEFT JOIN invoicepayment ip
                     ON ip.invoiceno = ih.invoiceno
      WHERE dc.id = 1
            AND ip.paymenttypeid IN (1, 2, 3, 4)
            AND ip.invoicedate BETWEEN '2018/05/01'
                                       AND '2018/06/19'
            AND ih.deleted = 0;

請注意，這UNION消除了重複。所以我添加DISTINCT了在這個版本中也消除了重複項。刪除它，如果實際上並不想要這種副作用UNION或者如果不能有任何重複（我沒有你的架構的完整圖片來判斷這一點。）。

但是，它只是一個較短的查詢文本。根據現有索引，您的第一種方法可能執行得更快。如有疑問，請檢查執行計劃。

引用自：https://dba.stackexchange.com/questions/210012