业务描述: 业务主表(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个字: 拙技