这些问题应该涉及的都是比较基础的sql知识,不必想的太复杂。
具体的代码:
-- 问题1
select spmc,splx,spdw,qsrq,jsrq,xsdj from csb_sp where qsrq >= '2021-01-1' ;
-- 问题2
select b.spmc,b.splx,sum(a.rksl) as rkzl,count(a.spbh) as rkcs,sum(a.grdj) as zczje from sp_rkjl a
left join csb_sp b on a.spbh = b.spbh
where rkrq >= '2021-01-1'
group by b.spmc,b.splx order by sum(a.rksl) desc ;
-- 问题3 这个需要考虑单价变动的情况
select b.spmc,b.splx,sum(jysl) as jyzl,sum(a.jysl*b.xsdj) as jyzje from sp_jyjl a
left join csb_sp b on a.spbh = b.spbh and a.jyrq between b.qsrq and b.jsrq
where a.jyrq between '2021-03-01' and '2021-03-31'
group by b.spmc,b.splx order by sum(a.jysl*b.xsdj) desc
-- 问题4
select right(date_format(jyrq, '%Y%m'),2),sum(case when fkfs = 0 then 1 else 0 end ) xjcs
,sum(fkfs) as fxjfkcs --基于fkfs这个字段是int类型的写法
,sum(case when fkfs = 0 then a.jysl*b.xsdj else 0 end )/sum(a.jysl*b.xsdj) xjfrjyjezb
from sp_jyjl a
left join csb_sp b on a.spbh = b.spbh and a.jyrq between b.qsrq and b.jsrq
where right(date_format(jyrq, '%Y%m'),2) between '01' and '03'
group by right(date_format(jyrq, '%Y%m'),2) order by right(date_format(jyrq, '%Y%m'),2)
-- 问题5
select t1.spmc,t1.splx,avg(jyzl) ,avg(jyzje ) from (
select a.jyrq,b.spmc,b.splx,sum(jysl) as jyzl,sum(a.jysl*b.xsdj) as jyzje from sp_jyjl a
left join csb_sp b on a.spbh = b.spbh and a.jyrq between b.qsrq and b.jsrq
where a.jyrq >= '2021-01-01'
group by b.spmc,b.splx ,a.jyrq ) t1
group by t1.spmc,t1.splx having avg(jyzl) >=100 or avg(jyzje )>=3000
由于没有数据内容和表结构可以参考,所写内容均需后续验证,如有问题,请直接联系。