傅军超(提问者)跟sql没啥关系吧，我的sql很长
select (case
when (SUBSTR(a.日期, 1, 7)=SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and (b.收入-a.成本)<0)
or (SUBSTR(a.日期, 1, 7)<>SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and d1.财务调整利润<0)
then \'本月亏损公司\'
when (SUBSTR(a.日期, 1, 7)=SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and c.期初上年月均 is not null and(b.收入-a.成本)<
c.期初上年月均/to_char(last_day(sysdate),\'dd\')*to_char(sysdate,\'dd\'))
or ((SUBSTR(a.日期, 1, 7)=SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and c.期初上年月均 is null) and
(b.收入-a.成本)SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and c.期初上年月均 is not null and d1.财务调整利润SUBSTR(to_char(sysdate,\'yyyy-mm-dd\'), 1, 7) and c.期初上年月均 is null and d1.财务调整利润greatest(nvl(e2.财务调整利润最好年/12,0),nvl(c2.期初最好年,0))/to_char(last_day(sysdate),\'dd\')*to_char(sysdate,\'dd\')
then \'本月利润超最好年公司\'
else \'其他\'end) as 类别,
aa.gongsi,aa.fgongsi,a.日期,(b.收入-a.成本) 利润,c.期初上年月均,c1.期初前年月均,c2.期初最好年,d.财务调整利润上月,d1.财务调整利润,
e.财务调整利润去年/12 财务调整利润去年月均,e1.财务调整利润前年/12 财务调整利润前年月均,greatest(e2.财务调整利润最好年/12,c2.期初最好年) 最好年,
(b1.上月收入-a1.上月成本) 上月利润,(b2.上年收入-a2.上年成本)/12 上年月均利润,a1.上月成本,b1.上月收入,e2.财务调整利润最好年
from
(
select distinct b.gongsi,b.fgongsi,b.chejian,b.fuzeren from tj_zuzhi_t b left join
bpminsttasks c on b.taskid=c.taskid where (c.state=\'Running\'or c.state=\'Approved\')
) aa
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 7) 日期,sum(a.jine) 成本 from
(select a.*,b.jine,c.* from tj_chengben_m a left join tj_chengben_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE SUBSTR(A.RIQI, 1, 7) = substr(\'${abc}\', 1, 7) group by a.gongsi,SUBSTR(a.riqi, 1, 7)
) a--本月成本
on aa.gongsi=a.gongsi
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 7) 日期,sum(a.jine) 上月成本 from
(select a.*,b.jine,c.* from tj_chengben_m a left join tj_chengben_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE to_char(add_months(to_date(SUBSTR(a.RIQI, 1, 7),\'yyyy-mm\'),1),\'yyyy-mm\') = substr(\'${abc}\', 1, 7)
group by a.gongsi,SUBSTR(a.riqi, 1, 7)
) a1--上月成本
on a.gongsi=a1.gongsi
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 4) 日期,sum(a.jine) 上年成本 from
(select a.*,b.jine,c.* from tj_chengben_m a left join tj_chengben_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE SUBSTR(A.RIQI, 1, 4)+1 = substr(\'${abc}\', 1, 4) group by a.gongsi,SUBSTR(a.riqi, 1, 4)
) a2--上年成本
on a.gongsi=a2.gongsi
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 7) 日期,sum(a.jine) 收入 from
(select a.*,b.jine,c.* from tj_shouru_m a left join tj_shouru_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE SUBSTR(a.RIQI, 1, 7) = substr(\'${abc}\', 1, 7) group by a.gongsi,SUBSTR(a.riqi, 1, 7)
) b--本月收入
on a.gongsi=b.gongsi
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 7) 日期,sum(a.jine) 上月收入 from
(select a.*,b.jine,c.* from tj_shouru_m a left join tj_shouru_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE to_char(add_months(to_date(SUBSTR(a.RIQI, 1, 7),\'yyyy-mm\'),1),\'yyyy-mm\') = substr(\'${abc}\', 1, 7)
group by a.gongsi,SUBSTR(a.riqi, 1, 7)
) b1--上月收入
on a.gongsi=b1.gongsi
left join
(select a.gongsi,SUBSTR(a.riqi, 1, 4) 日期,sum(a.jine) 上年收入 from
(select a.*,b.jine,c.* from tj_shouru_m a left join tj_shouru_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') a
WHERE SUBSTR(A.RIQI, 1, 4)+1 = substr(\'${abc}\', 1, 4) group by a.gongsi,SUBSTR(a.riqi, 1, 4)
) b2--上年收入
on a.gongsi=b2.gongsi
left join
(select b.gongsi,b.lirun 期初上年月均,b.niandu
from tj_lishicaiwu_t b left join
bpminsttasks c on b.taskid=c.taskid where (c.state=\'Running\'or c.state=\'Approved\')and b.niandu+1 = substr(\'${abc}\', 1, 4)
) c--期初上年月均
on a.gongsi=c.gongsi
left join
(select b.gongsi,b.lirun 期初前年月均,b.niandu
from tj_lishicaiwu_t b left join
bpminsttasks c on b.taskid=c.taskid where (c.state=\'Running\'or c.state=\'Approved\')and b.niandu+2 = substr(\'${abc}\', 1, 4)
) c1--期初前年月均
on a.gongsi=c1.gongsi
left join
(select b.gongsi,max(b.lirun) 期初最好年
from tj_lishicaiwu_t b left join
bpminsttasks c on b.taskid=c.taskid where (c.state=\'Running\'or c.state=\'Approved\') group by b.gongsi
) c2--最好年
on a.gongsi=c2.gongsi
left join
(select SUBSTR(b.riqi, 1, 7) 日期,b.gongsi,b.lirun 财务调整利润上月 from
( select a.riqi,b.*,c.* from tj_lirun_m a left join tj_lirun_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') b
WHERE to_char(add_months(to_date(SUBSTR(b.RIQI, 1, 7),\'yyyy-mm\'),1),\'yyyy-mm\') = substr(\'${abc}\', 1, 7)
) d--财务调整利润上月
on a.gongsi=d.gongsi
left join
(select SUBSTR(b.riqi, 1, 7) 日期,b.gongsi,b.lirun 财务调整利润 from
( select a.riqi,b.*,c.* from tj_lirun_m a left join tj_lirun_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') b
WHERE SUBSTR(b.RIQI, 1, 7) = substr(\'${abc}\', 1, 7)
) d1--财务调整利润上月
on a.gongsi=d1.gongsi
left join
(
select SUBSTR(b.riqi, 1, 4) 日期,b.gongsi,sum(b.lirun) 财务调整利润去年
from
( select a.riqi,b.* from tj_lirun_m a left join tj_lirun_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') b
WHERE SUBSTR(b.RIQI, 1, 4)+1 = substr(\'${abc}\', 1, 4) group by SUBSTR(b.riqi, 1, 4),b.gongsi
) e--财务调整利润去年
on a.gongsi=e.gongsi
left join
(
select SUBSTR(b.riqi, 1, 4) 日期,b.gongsi,sum(b.lirun) 财务调整利润前年
from
( select a.riqi,b.* from tj_lirun_m a left join tj_lirun_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') b
WHERE SUBSTR(b.RIQI, 1, 4)+2 = substr(\'${abc}\', 1, 4) group by SUBSTR(b.riqi, 1, 4),b.gongsi
) e1--财务调整利润去年
on a.gongsi=e.gongsi
left join
(
select gongsi,max(财务调整利润1) 财务调整利润最好年 from (
select SUBSTR(b.riqi, 1, 4) 日期,b.gongsi,sum(b.lirun) 财务调整利润1
from
( select a.riqi,b.* from tj_lirun_m a left join tj_lirun_t b on a.taskid=b.taskid left join
bpminsttasks c on a.taskid=c.taskid where c.state=\'Running\'or c.state=\'Approved\') b
group by SUBSTR(b.riqi, 1, 4),b.gongsi)
group by gongsi
) e2--财务调整利润1
on a.gongsi=e2.gongsi
1min目标场景问卷
立即参与
