一个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个字: 拙技

巴特西

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

最新文章

热门文章