sql - 如何聚合表达式的结果？

Question

我试图在调整 docBal 后获取每个公司的总和(docBal)。但是当我运行下面的查询时，我被迫在 groupby 中包含 doctype 和 docbal - 这是我不想要的。如何修改此查询？

 select 
  DocBal =  CASE d.DocType WHEN 'AD' THEN -d.DocBal
                           ELSE d.DocBal END,
  Sum(DocBal)
  from Vendor v
   inner join APDoc d
   on v.VendId = d.VendId
   where 
      d.PerPost = 201307
      and d.DocType in ('VO','AP','AD')
      and d.OpenDoc = 1
      and Acct = 210110
     group by CpnyID

Answer 1

您可以使用子选择来捕获所需的信息。

    SELECT  S.CpnyID,
            SUM(S.DocBal)
    FROM    (
            SELECT  CpnyID,
                    CASE d.DocType WHEN 'AD' THEN -d.DocBal ELSE d.DocBal END AS DocBal
            FROM    Vendor v
                    INNER JOIN APDoc d ON v.VendId = d.VendId
            WHERE     d.PerPost = 201307
            AND d.DocType in ('VO','AP','AD')
            AND d.OpenDoc = 1
            AND Acct = 210110
    ) AS S
    GROUP BY S.CpnyID

这应该为您提供所需的数据，而无需在聚合函数中包含其他列。