SQL绕口令
2022/7/13 2:20:22
本文主要是介绍SQL绕口令,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
select yearmonth, type, name, last_buy_month as 上次购进时间, sum(A去年购进总金额) as A去年购进总金额, sum(B去年购进总金额) as B去年购进总金额, sum(C去年购进总金额) as C去年购进总金额, sum(D去年购进总金额) as D去年购进总金额, sum(A上次购进总金额) as A上次购进总金额, sum(B上次购进总金额) as B上次购进总金额, sum(C上次购进总金额) as C上次购进总金额, sum(D上次购进总金额) as D上次购进总金额 from ( select '202206' as yearmonth, c.name, sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and b.category_name = 'OTC-A' then a.amount else 0 end ) as A去年购进总金额, sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and b.category_name = 'OTC-B' then a.amount else 0 end ) as B去年购进总金额, sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and b.category_name = 'OTC-C' then a.amount else 0 end ) as C去年购进总金额, sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and b.category_name = 'OTC-D' then a.amount else 0 end ) as D去年购进总金额, d.last_buy_month, sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-A' then a.amount else 0 end ) as A上次购进总金额, sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-B' then a.amount else 0 end ) as B上次购进总金额, sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-C' then a.amount else 0 end ) as C上次购进总金额, sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-D' then a.amount else 0 end ) as D上次购进总金额, case when c.level1 in ('3001','3002','6030') then '连锁' when a.label @> '{14}' then '总经理关注' when a.label @> '{16}' then '大区样板' when a.label @> '{15}' then '大区重点' when c.level1 in ('3003') then '单点' when c.level1 in ('4001','4002','4003','2032','2033','2086','2087') then '诊所' when c.level1 in ('1004') then '非协议' end as type,a.newdepartmentid from flowmonth a join goods b on a.goodscode = b.code join client c on a.clientid = c.id left join ( select clientid, max(case when yearmonth < to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and amount > 0 then yearmonth end ) as last_buy_month from flowmonth a where sfcx = '1' and exists (select clientid from flowmonth where yearmonth = '202206' and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '1 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) GROUP BY clientid ) d on a.clientid = d.clientid where sfcx = '1' and b.category_name like 'OTC-%' and exists (select clientid from flowmonth where yearmonth = '202206' and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '1 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null)) and ( c.level1 in ('3001','3002','6030','3003','3003','4001','4002','4003','2032','2033','2086','2087','1004') or a.label @> '{14}' or a.label @> '{15}' or a.label @> '{16}') GROUP BY c.name,d.last_buy_month,c.level1,a.label,a.newdepartmentid ) a join view_person_department_otc d on a.newdepartmentid = d.departmentid GROUP BY yearmonth,name,last_buy_month,type;
这篇关于SQL绕口令的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?
- 2024-05-09企业src漏洞挖掘-有意思的命令执行
- 2024-05-08阿里云域名注册流程,分享给第一次购买域名的新手站长!