You need to enable JavaScript to run this app. 在现有WIP交易统计SQL中新增已开票、未开票汇总列的实现方法
优惠活动
大模型
产品
解决方案
定价
更多

在现有WIP交易统计SQL中新增已开票、未开票汇总列的实现方法

需补充的两段代码

  • 逐行属性判断代码(用于给每条WIP交易标记对应金额归属):
    case when WIPTransaction.Invoicenumber is not null then WIPTransaction.Amount else 0 end 'INVOICED',
    case when WIPTransaction.Invoicenumber is null then WIPTransaction.Amount else 0 end 'UNINVOICED'
    
  • 分组汇总代码(用于按FeeEarnerID聚合计算两类金额的总和):
    sum(aa.INVOICED) Invoiced,
    sum(aa.UNINVOICED) Uninvoiced
    

代码放置的精确位置

  • 逐行判断代码:放在内层嵌套子查询(最终别名为aa的SELECT块)的字段列表中,紧跟原有CREDITORS字段的CASE计算语句后面,修改原有语句末尾的标点为逗号后插入即可。这里是逐行遍历每条WIP交易数据的环节,刚好可以直接读取Invoicenumber的空值状态给金额打标,和现有FEES、DISB、CREDITORS的拆分计算逻辑完全统一,不需要调整原有JOIN关联、日期过滤的逻辑。
  • 分组汇总代码:放在最外层主查询的SELECT字段列表最末尾,紧跟原有sum(aa.CREDITORS) Creditors语句的后面,作为结果集的最后两列。因为这部分是对aa子查询结果按FeeEarnerID分组后的聚合计算,不需要修改原有GROUP BY的规则,不会影响现有字段的计算逻辑。

修正后的完整SQL参考

select
  FeeEarnerID, 
  (
    select
      (select [name] from [User] AS u where u.userid=f.userid)
    from
      feeearner AS f
    where
      f.FeeEarnerID=aa.FeeEarnerID
  ) FeeEarner, 
  sum(aa.FEES) Fees,
  sum(aa.DISB) Disbursements,
  sum(aa.CREDITORS) Creditors,
  -- 新增的两列聚合结果
  sum(aa.INVOICED) Invoiced,
  sum(aa.UNINVOICED) Uninvoiced
from
(
  SELECT
    FeeEarner.FeeEarnerID, 
    case when WIPTransaction.WIPTransactionTypeID IN (1,17,18,20,21,25)  then WIPTransaction.Amount else 0 end 'FEES',
    case when WIPTransaction.WIPTransactionTypeID IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29)  then WIPTransaction.Amount else 0 end 'DISB',
    case when WIPTransaction.WIPTransactionTypeID IN (24)  then WIPTransaction.Amount else 0 end 'CREDITORS',
    -- 新增的逐行判断字段
    case when WIPTransaction.Invoicenumber is not null then WIPTransaction.Amount else 0 end 'INVOICED',
    case when WIPTransaction.Invoicenumber is null then WIPTransaction.Amount else 0 end 'UNINVOICED'
  FROM
  (
    (
      FeeEarner
    JOIN
      WIPTransaction ON FeeEarner.FeeEarnerID = WIPTransaction.FeeEarnerID
    )
  JOIN
    WIPTransactionType ON WIPTransactionType.WIPTransactionTypeID = WIPTransaction.WIPTransactionTypeID
  )
  WHERE
    (WIPTransaction.TransactionDate BETWEEN '2020-10-01' AND '2020-12-31')
)
  AS aa
group by
  FeeEarnerID

内容的提问来源于stack exchange,提问作者CrackkiiCrazy

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.05 02:15:03