复杂sql:oracle ，数据库中字段名有空值的查询

最新推荐文章于 2025-04-29 13:29:22 发布

赤蓝基族长

最新推荐文章于 2025-04-29 13:29:22 发布

阅读量914

点赞数

CC 4.0 BY-SA版权

分类专栏：公司成长文章标签： java

本文链接：https://blog.csdn.net/qq_43344595/article/details/124272220

公司成长专栏收录该内容

16 篇文章

订阅专栏

SELECT
t. qty,
t. amt,
t.suppName,
t.partGroup,
t.lateQty,
(ROUND((t.qty-t.lateQty)/t.qty * 100, 1))||'%' AS orderFillRate
FROM
(
SELECT
            SP.SUPP_NAME suppName,
          PI.PART_GROUP  partGroup,
			    --TP.PO_DATE+SR.PURCHASING_CYCLE*7 poDates,--生效日期
				-- TI.IN_DATE inDate, --收获日期
				--SR.PURCHASING_CYCLE
	--CASE WHEN TO_NUMBER(TO_DATE(TI.IN_DATE,'yyyy-mm-dd hh24:mi:ss')-TO_DATE(TP.PO_DATE,'yyyy-mm-dd hh24:mi:ss'))>SR.PURCHASING_CYCLE*7  THEN TI.QTY 	ELSE  TI.QTY='0' END  lateQty，
	SUM(CASE WHEN TI.IN_DATE>TP.PO_DATE+SR.PURCHASING_CYCLE*7  THEN TI.qty 	ELSE 0 END) AS lateQty,

 SUM( CASE WHEN TI.MOVEMENT_TYPE='101'  THEN -1*TI.QTY WHEN TI.MOVEMENT_TYPE='161'  THEN -1*TI.QTY 	ELSE  TI.QTY END ) AS qty ,
		
	SUM(CASE WHEN TI.MOVEMENT_TYPE='101'  THEN -1*TI.AMT WHEN TI.MOVEMENT_TYPE='161'  THEN -1*TI.AMT 	ELSE  TI.AMT END) AS amt
		
	
		
		
        FROM
            TR_INBOUND TI
           LEFT JOIN MD_PART_INFO PI
        ON PI.PART_NO=TI.PART_NO AND PI.DEL_FLAG = '0'
          LEFT JOIN MD_SUPPLIER SP
         ON SP.SUPP_CODE=TI.SUPP_CODE AND  SP.DEL_FLAG = '0'
				  LEFT JOIN TR_PO TP
                          ON TP.PO_NO=TI.PO_NO AND TP.PO_ITEM_NO=TI.PO_ITEM_NO AND TP.DEL_FLAG = '0'
					 LEFT JOIN MD_SUPP_PART SR
         ON SR.SUPP_CODE=TI.SUPP_CODE AND SR.PART_NO=TI.PART_NO AND SR.DEL_FLAG = '0'
				 
           WHERE
          TI.DEL_FLAG = '0'
           AND
            TI.MOVEMENT_TYPE in (101,161,102,162)
          AND
          TI.CREATE_DATE >= TRUNC(NEXT_DAY(SYSDATE-8,1)-6) AND TI.CREATE_DATE  < TRUNC(NEXT_DAY(SYSDATE-8,1)+1)
					
					GROUP BY SP.SUPP_NAME,PI.PART_GROUP
					
					) t

        select
        pa.PART_GROUP,
        pa.PART_TYPE,
        pa."AMT_IN MONTH_1",
        pa."AMT_IN MONTH_2",
        pa."AMT_IN MONTH_3",
        pa."AMT_IN MONTH_4",
        pa."AMT_IN MONTH_5",
        pa."AMT_IN MONTH_6",
        pa."AMT_IN MONTH_7",
        pa."AMT_IN MONTH_8",
        pa."AMT_IN MONTH_9",
        pa."AMT_IN MONTH_10",
        pa."AMT_IN MONTH_11",
        pa."AMT_IN MONTH_12"
        from
        RP_PO_AMT pa
        where
        pa.report_no = #{reportNo}
        order by
        pa.PART_GROUP