通过设置变量在子查询和父查询之间共享参数-----------------SQLServer

最新推荐文章于 2022-01-17 16:48:48 发布

多哥仁慈的骆驼

最新推荐文章于 2022-01-17 16:48:48 发布

阅读量427

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_36381640/article/details/105073370

版权

数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

create table biz_recovery_report
(
    id          bigint identity primary key,
    version     bigint      not null,
    rec_id      varchar(50) not null,
    del_flag    varchar(2),
    insert_id   varchar(50),
    insert_time datetime2,
    month       varchar(50),
    recovery    numeric(19, 2),
    sale        numeric(19, 2),
    update_id   varchar(50),
    update_time datetime2,
    year        varchar(50),
    bu          varchar(50),
    slrable     numeric(19, 2)
)
go

                            declare @year int=?
                            declare @bu varchar(10) =?
                            select  (sum(pp.mustRec)/24)/(select sum(a.sale) from biz_recovery_report a where a.year=@year and a.bu=@bu)*360 *
                                    (select sum(p.sale)/( select sum(p1.sale)  from biz_recovery_report p1 where   p1.bu=@bu  ) from biz_recovery_report p  where  p.year=@year  and p.bu=@bu  ) as rs,
                                    (select  distinct p2.dic_ch_name from sys_dic as  p2 where p2.dic_value=CAST(@bu as varchar(10)) and  p2.dic_type='9000' and p2.dele_flag=0) as bu
                            from (
                                       select
                                               m1.mustRec  as  mustRec,
                                              m1.bu
                                       from (
                                             select   sum(slrable) as mustRec,max(bu)  bu
                                             from biz_recovery_report
                                             where bu=@bu and  (year=@year-1 or year=@year ) and month=12
                                             group by year,month
                                            ) as m1
                                       union all
                                       select  m2.mustRec mustRec ,m2.bu
                                       from (
                                               select   sum(slrable)*2 as mustRec,max(bu)  bu
                                               from biz_recovery_report
                                               where bu=@bu and year=@year and month>=1 and  month<=11
                                               group by year,month
                                           ) as m2
                            ) pp