如何使查询更简单

Question

  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 

“金额”具有已支付的值，而PaymentTypeId具有支付类型。我试图将数据检索到条件为PaymentTypeId =1、2、3或4的4个节点，这是我得到的查询。这个任务有什么简单的方法吗？

Answer 1

那么，至少你似乎可以摆脱UNIONs。使用CASE ... END来获取cashamount、creditnote、chequeamount和credit的值，这取决于paymenttypeid。使用IN (或多个OR)筛选WHERE子句中的paymenttypeid。

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的副作用实际上并不需要，或者如果没有任何副本(我没有您的模式的完整图片来判断这一点)。

但是，它只是一个较短的查询文本。根据现有索引的不同，第一种方法可能执行得更快。当有疑问时，检查执行计划。