一个sql和C#代码结合的分组求和的查询

 

 业务描述:  业务主表(tab_main 主键 id), 供应商名称(supplier), 金额(amount 需要统计求和),还有分类( 有单独的表categoryid),集中采购标记字段(tenderMark),审核时间(auditTime)

                  分类表: (tab_category  ),分类id: categoryid,分类名称: categoryname 

主表是明细数据, 要对这个明细 进行统计, 固定列: 供应商名称,总金额,集采金额, 后面是各个分类的列,有多少个分类,就有多少列(动态的)

现在写这个sql 思路:

 

(1) 基础查询 sourceData 

   with sourceData as (

     select a.supplier, a.amount,  (case when a.tenderMark=1 then a.amount else 0 end) as tenderAmount,a.categoryid

     where a.auditTime between  to_date('2022-01-01 00:00:00',yyyy-mm-dd hi24:mi:ss) and  to_date('2022-03-01 00:00:00',yyyy-mm-dd hi24:mi:ss) 

 ) 

 

(2)根据分类  构造 数据集 dataALL 

     with dataALL as (

                  select  1 as Category0, 0 as Category1, 0 as Category2, 0 as Category3, T.*   from sourceData T  where categoryid=1

  union all    select  0 as Category0, 1 as Category1, 0 as Category2, 0 as Category3, T.*   from sourceData T  where categoryid=2

  union all    select  0 as Category0, 0 as Category1, 1 as Category2, 0 as Category3, T.*   from sourceData T  where categoryid=3

  union all    select  0 as Category0, 0 as Category1, 0 as Category2, 1 as Category3, T.*   from sourceData T  where categoryid=3

                ... --动态的, 有多少个分类 就 重复拼接,通过C#代码实现,  构建对应的列数据

   )  

 

(4)根据分类,group by 

 

    select  supplier,sum(amount) as amountSum , sum(tenderAmount) as tenderAmountSum

       ,(case when Category0=1 then sum(amount) else 0 end)   as Category0AmountSum

       ,(case when Category1=1 then sum(amount) else 0 end)   as Category1AmountSum

       ,(case when Category2=1 then sum(amount) else 0 end)   as Category2AmountSum

       ,(case when Category3=1 then sum(amount) else 0 end)   as Category3AmountSum

      ... --动态拼接

       from dataALL  group by supplier,Category0,Category1,Category2,Category3

 

(5) 之前以为上面就写完了,后来发现 部署到正式上, 一个供应商对应多条数据出来,

      原来当 一个 供应商 有多个分类的时候,数据就会....

    那就继续group by 合并一下 

 

    select supplier, sum(amountSum ) amountSum , sum(tenderAmountSum) tenderAmountSum

          ,sum(Category0AmountSum) Category0AmountSum  

          ,sum(Category1AmountSum) Category1AmountSum  

          ,sum(Category2AmountSum) Category2AmountSum  

          ,sum(Category3AmountSum) Category3AmountSum  

        ... --动态拼接

  from (上面的(4)的查询 )  newTab

  group by supplier

  order by supplier asc

 

测试一下数据, 没问题了,就可以部署了.

 

上面标记颜色的部分, 是可以用 C#代码 进行 循环填充的, 多定义几个 StringBuilder   循环 拼接字符串.

 

 

小结一下: 将复杂的问题 简单化, 分类为 3个的时候怎么写, 4个的时候 怎么写,然后推断成 动态的怎么拼接 sql.

 

PS: (1) 我知道有其他的实现方式, 或者什么 行转列的 写法 ,但是好在 我知道我这个业务的分类 不会很多(一般4到6个,不会超过7个)         

           就这样简单写一下,效率也还行,凑合用,实现效果就行. 

 

      (2) 先把数据取出来,然后用C# 代码 分组合并, 也是一个思路 ,但是这个 有悖于 开发规范.

           一般 数据能在数据库 里面操作的, 直接数据库 里面操作,数据库不方便操作的, 拿出来用 C# 代码补充操作. 

 

 

 我给这个写法批2个字: 拙技