nc6.5收发存汇总表查询sql

作者: 猛犸象和剑齿虎 | 来源:发表于2024-02-26 10:25 被阅读0次

史上最全MySQL架构，助你深度掌握MySQL开发管理，赢在大数
nosql
一怒之下，开发了一款IDEA插件 [Mybatis Log Pl
EF Core 备忘
ThinkPHP查询
数据分析之SQL子查询
JPA 动态生成sql
sqlserver，oracle，mysql分页查询通用sql
无限极分类-全路径方式
定位和处理执行慢的sql

--计算结存量字段
select t3.code,t3.name 物料名称,t3.materialspec 规格,t3.materialtype 型号,t4.name 物料分类,t2.name 公司,t1.nbeginnum,t1.NTERMINNUM,t1.NTERMOUTNUM,
nvl(t1.nbeginnum,0)+nvl(t1.NTERMinNUM,0)-nvl(t1.NTERMOUTNUM,0) as 结存 from 
(
select a.CMATERIALOID,a.pk_group,a.pk_org,a.nbeginnum,b.NTERMINNUM,b.NTERMOUTNUM from 
(
--------期初--------------------
SELECT
    begindata.cmaterialoid,
    begindata.pk_group,
    begindata.pk_org,
    sum( nvl( begindata.nnum, 0.0 ) ) nbeginnum,
    sum( nvl( begindata.nassistnum, 0.0 ) ) nbeginastnum,
    sum( nvl( begindata.ngrossnum, 0.0 ) ) nbegingrossnum
FROM
    (
    SELECT
        inithand.pk_group pk_group,
        inithand.pk_org pk_org,
        inithand.cmaterialoid cmaterialoid,
        sum( nvl( inithand.nnum, 0.0 ) ) nnum,
        sum( nvl( inithand.nassistnum, 0.0 ) ) nassistnum,
        sum( nvl( inithand.ngrossnum, 0.0 ) ) ngrossnum
    FROM
        (
        SELECT
            hand.pk_group pk_group,
            hand.pk_org pk_org,
            hand.cmaterialoid cmaterialoid,
            hand.nnum nnum,
            hand.nassistnum nassistnum,
            hand.ngrossnum ngrossnum 
        FROM
            ic_month_hand hand 
        WHERE
            1 = 1 
            AND hand.dr = 0 
            AND hand.pk_group = '0001O110000000000IIT' 
            AND hand.dyearmonth = '2019-12' 
            AND ( hand.pk_org = '0001O1100000000027VN' ) 
            AND ( hand.pk_group = '0001O110000000000IIT' ) 
            AND ( hand.pk_group = '0001O110000000000IIT' ) UNION ALL
        SELECT RECORD
            .pk_group pk_group,
            RECORD.pk_org pk_org,
            RECORD.cmaterialoid cmaterialoid,
            nvl( RECORD.ninnum, 0.0 ) - nvl( RECORD.noutnum, 0.0 ) nnum,
            nvl( RECORD.ninassistnum, 0.0 ) - nvl( RECORD.noutassistnum, 0.0 ) nassistnum,
            nvl( RECORD.ningrossnum, 0.0 ) - nvl( RECORD.noutgrossnum, 0.0 ) ngrossnum 
        FROM
            ic_month_record
            RECORD INNER JOIN ic_transtype transtype ON ( RECORD.ctrantypeid = transtype.ctrantypeid ) 
        WHERE
            1 = 1 
            AND RECORD.dr = 0 
            AND RECORD.pk_group = '0001O110000000000IIT' 
            AND RECORD.dyearmonth >= '2020-01' 
            AND RECORD.dyearmonth <= '2020-04' 
            AND transtype.baffectonhand = 'Y' 
            AND (
                nvl( RECORD.ninnum, 0.0 ) - nvl( RECORD.noutnum, 0.0 ) <> 0.0 
                OR nvl( RECORD.ninassistnum, 0.0 ) - nvl( RECORD.noutassistnum, 0.0 ) <> 0.0 
                OR nvl( RECORD.ningrossnum, 0.0 ) - nvl( RECORD.noutgrossnum, 0.0 ) <> 0.0 
            ) 
            AND ( RECORD.pk_org = '0001O1100000000027VN' ) 
            AND ( RECORD.pk_group = '0001O110000000000IIT' ) 
            AND ( RECORD.pk_group = '0001O110000000000IIT' ) UNION ALL
        SELECT
            flowallview.pk_group pk_group,
            flowallview.pk_org pk_org,
            flowallview.cmaterialoid cmaterialoid,
            nvl( flowallview.ninnum, 0.0 ) - nvl( flowallview.noutnum, 0.0 ) nnum,
            nvl( flowallview.ninassistnum, 0.0 ) - nvl( flowallview.noutassistnum, 0.0 ) nassistnum,
            nvl( flowallview.ningrossnum, 0.0 ) - nvl( flowallview.noutgrossnum, 0.0 ) ngrossnum 
        FROM
            (
            SELECT
                flow.pk_group pk_group,
                flow.pk_org pk_org,
                flow.cmaterialoid cmaterialoid,
                flow.ninnum ninnum,
                flow.ninassistnum ninassistnum,
                flow.ningrossnum ningrossnum,
                flow.noutnum noutnum,
                flow.noutassistnum noutassistnum,
                flow.noutgrossnum noutgrossnum 
            FROM
                ic_flow flow
                INNER JOIN ic_transtype transtype ON ( flow.ctrantypeid = transtype.ctrantypeid AND transtype.baffectonhand = 'Y' ) 
            WHERE
                1 = 1 
                AND flow.dr = 0 
                AND flow.dbizdate >= '2020-05-01 00:00:00' 
                AND flow.dbizdate <= '2024-01-31 23:59:59' 
                AND flow.pk_group = '0001O110000000000IIT' 
                AND ( flow.pk_org = '0001O1100000000027VN' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
            ) flowallview 
        ) inithand 
    GROUP BY
        inithand.pk_group,
        inithand.pk_org,
        inithand.cmaterialoid 
    ) begindata 
GROUP BY
    begindata.cmaterialoid,
    begindata.pk_group,
    begindata.pk_org
)a 
--------------------------------------

left join 
(
-------------出入库流水----------------------------
SELECT
    termdata.cmaterialoid,
    termdata.pk_group,
    termdata.pk_org,
    0.0 nbeginnum,
    0.0 nbeginastnum,
    0.0 nbegingrossnum,
    sum( nvl( termdata.nterminnum, 0.0 ) ) nterminnum,
    sum( nvl( termdata.nterminastnum, 0.0 ) ) nterminastnum,
    sum( nvl( termdata.ntermingorsstnum, 0.0 ) ) ntermingorsstnum,
    sum( nvl( termdata.ntermoutnum, 0.0 ) ) ntermoutnum,
    sum( nvl( termdata.ntermoutastnum, 0.0 ) ) ntermoutastnum,
    sum( nvl( termdata.ntermoutgorsstnum, 0.0 ) ) ntermoutgorsstnum,
    sum( nvl( termdata.nterminnum, 0.0 ) ) - sum( nvl( termdata.ntermoutnum, 0.0 ) ) nonhandnum,
    sum( nvl( termdata.nterminastnum, 0.0 ) ) - sum( nvl( termdata.ntermoutastnum, 0.0 ) ) nonhandastnum,
    sum( nvl( termdata.ntermingorsstnum, 0.0 ) ) - sum( nvl( termdata.ntermoutgorsstnum, 0.0 ) ) ngrossnum 
FROM
    (
    SELECT
        term.cmaterialoid cmaterialoid,
        term.pk_group pk_group,
        term.pk_org pk_org,
        sum( nvl( term.ninnum, 0.0 ) ) nterminnum,
        sum( nvl( term.ninassistnum, 0.0 ) ) nterminastnum,
        sum( nvl( term.ningrossnum, 0.0 ) ) ntermingorsstnum,
        sum( nvl( term.noutnum, 0.0 ) ) ntermoutnum,
        sum( nvl( term.noutassistnum, 0.0 ) ) ntermoutastnum,
        sum( nvl( term.noutgrossnum, 0.0 ) ) ntermoutgorsstnum 
    FROM
        (
        SELECT
            termflow.cmaterialoid cmaterialoid,
            termflow.pk_group pk_group,
            termflow.pk_org pk_org,
            nvl( termflow.ninnum, 0.0 ) ninnum,
            nvl( termflow.ninassistnum, 0.0 ) ninassistnum,
            nvl( termflow.ningrossnum, 0.0 ) ningrossnum,
            nvl( termflow.noutnum, 0.0 ) noutnum,
            nvl( termflow.noutassistnum, 0.0 ) noutassistnum,
            nvl( termflow.noutgrossnum, 0.0 ) noutgrossnum 
        FROM
            (
            SELECT
                flow.cmaterialoid cmaterialoid,
                flow.pk_group pk_group,
                flow.pk_org pk_org,
                nvl( flow.ninnum, 0.0 ) ninnum,
                nvl( flow.ninassistnum, 0.0 ) ninassistnum,
                nvl( flow.ningrossnum, 0.0 ) ningrossnum,
                nvl( flow.noutnum, 0.0 ) noutnum,
                nvl( flow.noutassistnum, 0.0 ) noutassistnum,
                nvl( flow.noutgrossnum, 0.0 ) noutgrossnum 
            FROM
                ic_flow flow
                INNER JOIN ic_transtype transtype ON ( flow.ctrantypeid = transtype.ctrantypeid AND transtype.baffectonhand = 'Y' ) 
            WHERE
                1 = 1 
                AND flow.dr = 0 
                AND ( flow.pk_org = '0001O1100000000027VN' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.dbizdate >= '2024-02-01 00:00:00' AND flow.dbizdate <= '2024-02-29 23:59:59' ) 
            ) termflow 
        ) term 
    GROUP BY
        term.cmaterialoid,
        term.pk_group,
        term.pk_org 
    ) termdata 
GROUP BY
    termdata.cmaterialoid,
    termdata.pk_group,
    termdata.pk_org
) b on a.CMATERIALOID=b.CMATERIALOID
--------------全外连接的方式获取本月新品-----------------------------------
union 
select b.CMATERIALOID,b.pk_group,b.pk_org,a.nbeginnum,b.NTERMINNUM,b.NTERMOUTNUM from 
(
--------期初--------------------
SELECT
    begindata.cmaterialoid,
    begindata.pk_group,
    begindata.pk_org,
    sum( nvl( begindata.nnum, 0.0 ) ) nbeginnum,
    sum( nvl( begindata.nassistnum, 0.0 ) ) nbeginastnum,
    sum( nvl( begindata.ngrossnum, 0.0 ) ) nbegingrossnum
FROM
    (
    SELECT
        inithand.pk_group pk_group,
        inithand.pk_org pk_org,
        inithand.cmaterialoid cmaterialoid,
        sum( nvl( inithand.nnum, 0.0 ) ) nnum,
        sum( nvl( inithand.nassistnum, 0.0 ) ) nassistnum,
        sum( nvl( inithand.ngrossnum, 0.0 ) ) ngrossnum
    FROM
        (
        SELECT
            hand.pk_group pk_group,
            hand.pk_org pk_org,
            hand.cmaterialoid cmaterialoid,
            hand.nnum nnum,
            hand.nassistnum nassistnum,
            hand.ngrossnum ngrossnum 
        FROM
            ic_month_hand hand 
        WHERE
            1 = 1 
            AND hand.dr = 0 
            AND hand.pk_group = '0001O110000000000IIT' 
            AND hand.dyearmonth = '2019-12' 
            AND ( hand.pk_org = '0001O1100000000027VN' ) 
            AND ( hand.pk_group = '0001O110000000000IIT' ) 
            AND ( hand.pk_group = '0001O110000000000IIT' ) UNION ALL
        SELECT RECORD
            .pk_group pk_group,
            RECORD.pk_org pk_org,
            RECORD.cmaterialoid cmaterialoid,
            nvl( RECORD.ninnum, 0.0 ) - nvl( RECORD.noutnum, 0.0 ) nnum,
            nvl( RECORD.ninassistnum, 0.0 ) - nvl( RECORD.noutassistnum, 0.0 ) nassistnum,
            nvl( RECORD.ningrossnum, 0.0 ) - nvl( RECORD.noutgrossnum, 0.0 ) ngrossnum 
        FROM
            ic_month_record
            RECORD INNER JOIN ic_transtype transtype ON ( RECORD.ctrantypeid = transtype.ctrantypeid ) 
        WHERE
            1 = 1 
            AND RECORD.dr = 0 
            AND RECORD.pk_group = '0001O110000000000IIT' 
            AND RECORD.dyearmonth >= '2020-01' 
            AND RECORD.dyearmonth <= '2020-04' 
            AND transtype.baffectonhand = 'Y' 
            AND (
                nvl( RECORD.ninnum, 0.0 ) - nvl( RECORD.noutnum, 0.0 ) <> 0.0 
                OR nvl( RECORD.ninassistnum, 0.0 ) - nvl( RECORD.noutassistnum, 0.0 ) <> 0.0 
                OR nvl( RECORD.ningrossnum, 0.0 ) - nvl( RECORD.noutgrossnum, 0.0 ) <> 0.0 
            ) 
            AND ( RECORD.pk_org = '0001O1100000000027VN' ) 
            AND ( RECORD.pk_group = '0001O110000000000IIT' ) 
            AND ( RECORD.pk_group = '0001O110000000000IIT' ) UNION ALL
        SELECT
            flowallview.pk_group pk_group,
            flowallview.pk_org pk_org,
            flowallview.cmaterialoid cmaterialoid,
            nvl( flowallview.ninnum, 0.0 ) - nvl( flowallview.noutnum, 0.0 ) nnum,
            nvl( flowallview.ninassistnum, 0.0 ) - nvl( flowallview.noutassistnum, 0.0 ) nassistnum,
            nvl( flowallview.ningrossnum, 0.0 ) - nvl( flowallview.noutgrossnum, 0.0 ) ngrossnum 
        FROM
            (
            SELECT
                flow.pk_group pk_group,
                flow.pk_org pk_org,
                flow.cmaterialoid cmaterialoid,
                flow.ninnum ninnum,
                flow.ninassistnum ninassistnum,
                flow.ningrossnum ningrossnum,
                flow.noutnum noutnum,
                flow.noutassistnum noutassistnum,
                flow.noutgrossnum noutgrossnum 
            FROM
                ic_flow flow
                INNER JOIN ic_transtype transtype ON ( flow.ctrantypeid = transtype.ctrantypeid AND transtype.baffectonhand = 'Y' ) 
            WHERE
                1 = 1 
                AND flow.dr = 0 
                AND flow.dbizdate >= '2020-05-01 00:00:00' 
                AND flow.dbizdate <= '2024-01-31 23:59:59' 
                AND flow.pk_group = '0001O110000000000IIT' 
                AND ( flow.pk_org = '0001O1100000000027VN' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
            ) flowallview 
        ) inithand 
    GROUP BY
        inithand.pk_group,
        inithand.pk_org,
        inithand.cmaterialoid 
    ) begindata 
GROUP BY
    begindata.cmaterialoid,
    begindata.pk_group,
    begindata.pk_org
)a 
--------------------------------------

right join 
(
-------------出入库流水----------------------------
SELECT
    termdata.cmaterialoid,
    termdata.pk_group,
    termdata.pk_org,
    0.0 nbeginnum,
    0.0 nbeginastnum,
    0.0 nbegingrossnum,
    sum( nvl( termdata.nterminnum, 0.0 ) ) nterminnum,
    sum( nvl( termdata.nterminastnum, 0.0 ) ) nterminastnum,
    sum( nvl( termdata.ntermingorsstnum, 0.0 ) ) ntermingorsstnum,
    sum( nvl( termdata.ntermoutnum, 0.0 ) ) ntermoutnum,
    sum( nvl( termdata.ntermoutastnum, 0.0 ) ) ntermoutastnum,
    sum( nvl( termdata.ntermoutgorsstnum, 0.0 ) ) ntermoutgorsstnum,
    sum( nvl( termdata.nterminnum, 0.0 ) ) - sum( nvl( termdata.ntermoutnum, 0.0 ) ) nonhandnum,
    sum( nvl( termdata.nterminastnum, 0.0 ) ) - sum( nvl( termdata.ntermoutastnum, 0.0 ) ) nonhandastnum,
    sum( nvl( termdata.ntermingorsstnum, 0.0 ) ) - sum( nvl( termdata.ntermoutgorsstnum, 0.0 ) ) ngrossnum 
FROM
    (
    SELECT
        term.cmaterialoid cmaterialoid,
        term.pk_group pk_group,
        term.pk_org pk_org,
        sum( nvl( term.ninnum, 0.0 ) ) nterminnum,
        sum( nvl( term.ninassistnum, 0.0 ) ) nterminastnum,
        sum( nvl( term.ningrossnum, 0.0 ) ) ntermingorsstnum,
        sum( nvl( term.noutnum, 0.0 ) ) ntermoutnum,
        sum( nvl( term.noutassistnum, 0.0 ) ) ntermoutastnum,
        sum( nvl( term.noutgrossnum, 0.0 ) ) ntermoutgorsstnum 
    FROM
        (
        SELECT
            termflow.cmaterialoid cmaterialoid,
            termflow.pk_group pk_group,
            termflow.pk_org pk_org,
            nvl( termflow.ninnum, 0.0 ) ninnum,
            nvl( termflow.ninassistnum, 0.0 ) ninassistnum,
            nvl( termflow.ningrossnum, 0.0 ) ningrossnum,
            nvl( termflow.noutnum, 0.0 ) noutnum,
            nvl( termflow.noutassistnum, 0.0 ) noutassistnum,
            nvl( termflow.noutgrossnum, 0.0 ) noutgrossnum 
        FROM
            (
            SELECT
                flow.cmaterialoid cmaterialoid,
                flow.pk_group pk_group,
                flow.pk_org pk_org,
                nvl( flow.ninnum, 0.0 ) ninnum,
                nvl( flow.ninassistnum, 0.0 ) ninassistnum,
                nvl( flow.ningrossnum, 0.0 ) ningrossnum,
                nvl( flow.noutnum, 0.0 ) noutnum,
                nvl( flow.noutassistnum, 0.0 ) noutassistnum,
                nvl( flow.noutgrossnum, 0.0 ) noutgrossnum 
            FROM
                ic_flow flow
                INNER JOIN ic_transtype transtype ON ( flow.ctrantypeid = transtype.ctrantypeid AND transtype.baffectonhand = 'Y' ) 
            WHERE
                1 = 1 
                AND flow.dr = 0 
                AND ( flow.pk_org = '0001O1100000000027VN' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.pk_group = '0001O110000000000IIT' ) 
                AND ( flow.dbizdate >= '2024-02-01 00:00:00' AND flow.dbizdate <= '2024-02-29 23:59:59' ) 
            ) termflow 
        ) term 
    GROUP BY
        term.cmaterialoid,
        term.pk_group,
        term.pk_org 
    ) termdata 
GROUP BY
    termdata.cmaterialoid,
    termdata.pk_group,
    termdata.pk_org
) b on a.CMATERIALOID=b.CMATERIALOID

) t1
-----------------获取公司名材料名------------------------
left join  org_orgs t2  on  t1.PK_ORG=t2.PK_ORG
left join bd_material_v t3 on t1.CMATERIALOID=t3.pk_material
left join bd_marbasclass t4 on t3.pk_marbasclass =t4.pk_marbasclass

史上最全MySQL架构，助你深度掌握MySQL开发管理，赢在大数
前言 MySQL服务器由SQL层和存储引擎层构成。SQL层主要功能包括权限判断、SQL解析功能和查询缓存处理等，存...
nosql
内存存储用不着sql和用了sql也不行的时候，考虑nosqlnosql不适用于事务要求高和基于sql的结构化查询存...
一怒之下，开发了一款IDEA插件 [Mybatis Log Pl
起因近期项目开发中，涉及到一个高级查询的功能，SQL长达近百行，排查SQL问题时，需要拼接成可执行SQL。市面存...
EF Core 备忘
模糊查询sql linq 内连接查询sql linq 左连接查询sql linq 左连接查询（连接内带条件）sql...
ThinkPHP查询
查询方式表达式查询快捷查询区间查询组合查询统计查询动态查询 SQL查询 ThikPHP支持原生SQL查...
数据分析之SQL子查询
文章阅读路线： SQL子查询概念独立子查询实例相关子查询实例 SQL子查询常见玩伴 1.SQL子查询概念子查...
JPA 动态生成sql
1. 构建格式化时间查询sql：生成sql： 2. 构建多条件查询sql：生成sql： 3. 构建子查询：生...
sqlserver，oracle，mysql分页查询通用sql
摘要: sqlserver，oracle，mysql分页查询通用sql1：mysql通用查询sql /* sql:...
无限极分类-全路径方式
创建表SQL 测试数据组装SQL查询查询结果
定位和处理执行慢的sql
查询正在获取锁的sql 查询正在执行的sql 停止正在执行的sql