My SQL 子查询

文章目录

• • • 1 Introduction
    • 2 Detail
    • 3 Summary

1 Introduction

In the mysql there is query with before query result as conditon.

2 Detail

Result1

SELECT
	B.MBLNR "领料单号",     
	B.werks "工厂",     
	B.BUDAT_MKPF "领料日期",  
	b.CPUDT_MKPF "建单日期"   ,
	B.XBLNR_MKPF "发料单", 
	ZBAR_HEAD.ERDAT "发料日期" ,
	AFPO.PSMNG "生产数量",   
	AFPO.WEMNG "完工入库数量",  
	resb.BDMNG "应发数量",    
	B.MENGE "异动数量",       
	A.ZREASON "补料类型代码",    
	ZPPT0025.ZREAMSG "补料类型", 
	ZBAR_HEAD.BKTXT "补料备注",
	VBAK.VBELN "销售单号（按单生产）",       
	B.MATNR "原物料号",     
	A.MAKTX "物料描述",     
	A.AUFNR "生产工单",      
	VBAK.KUNNR "客户编号", 
	KNA1.NAME1 "客户名字" ,
	AFKO.PLNBEZ "成品物料号", 
	makt.MAKTX "成品物料名称" ,
	a.KOSTL    "成本中心"   
--	d.NAME1  "部门名称"
	
FROM
	NSDM_V_MSEG B
LEFT JOIN ZBAR_ITEM A ON
	B.XBLNR_MKPF = A.ID
	AND B.LINE_ID = A.CANUM
INNER JOIN ZBAR_head ON
	B.XBLNR_MKPF = ZBAR_head.ID
LEFT JOIN ZPPT0025 ON
	A.ZREASON = ZPPT0025.ZREASON
LEFT JOIN resb ON
	A.rsnum = resb.rsnum
	AND A.matnr = resb.matnr
	AND A.werks = resb.werks
	AND A.aufnr = resb.aufnr
	AND A.RSPOS = resb.RSPOS
LEFT JOIN AFPO ON
	A.AUFNR = AFPO.AUFNR
LEFT JOIN AFKO ON
	AFKO.AUFNR = AFPO.AUFNR
LEFT JOIN makt ON
	AFKO.PLNBEZ = makt.matnr
LEFT JOIN VBAK ON
	VBAK.VBELN = AFPO.KDAUF
LEFT JOIN KNA1  ON 	 KNA1.KUNNR  =VBAK.KUNNR
--LEFT JOIN cskt d on A.KOSTL  =   d.KOSTL 
WHERE
	B.BWART = 'Z61'
	AND TO_DATE(B.BUDAT_MKPF, 'YYYYMMDD') >= TO_DATE('20231101', 'YYYYMMDD')
	AND TO_DATE(B.BUDAT_MKPF, 'YYYYMMDD') < TO_DATE('20231201', 'YYYYMMDD')
	AND B.werks = '1161'

Result2

SELECT
	ekko.AEDAT,
	ekpo.matnr matnr,
	ekPo.ebeln,
	ekPo.NETPR,
	ekPo.PEINH,
	ekpo.WERKS WERKS
FROM
	ekko
INNER JOIN ekpo ON
	ekko.ebeln = ekpo.ebeln
WHERE
	ekpo.matnr = '000000000100096463'
	AND ekpo.WERKS = '1161'
ORDER BY 
	ekpo.AEDAT DESC,
	ekpo.EBELN DESC
LIMIT 1 OFFSET 0

We will use query result2 with result1 as conditon.
We merge it .and the result is displayed as follow:

SELECT
	*
FROM
	(
	SELECT
		ekPo.NETPR "价格",
		ekPo.PEINH "价格数量",
		B.MBLNR LLLL,     
		B.BUDAT_MKPF "领料日期",    
		B.XBLNR_MKPF "发料单", 
		AFPO.PSMNG "生产数量",   
		AFPO.WEMNG "完工入库数量",  
		resb.BDMNG "应发数量",    
		B.MENGE "异动数量",       
		A.ZREASON "补料类型代码",    
		ZPPT0025.ZREAMSG "补料类型", 
		ZBAR_HEAD.BKTXT "补料备注",
		VBAK.VBELN "销售单号（按单生产）",       
		B.MATNR "原物料号",     
		A.MAKTX "物料描述",     
		A.AUFNR "生产工单",      
		VBAK.KUNNR "客户编号",    
		AFKO.PLNBEZ "成品物料号", 
		makt.MAKTX "成品物料名称",
		ekko.AEDAT,
		ekpo.matnr matnr,
		ekPo.ebeln,
		ekpo.WERKS WERKS,
		ROW_NUMBER() OVER (PARTITION BY b.mblnr,
		b.xblnr_mkpf,
		vbak.vbeln,
		b.matnr,
		a.aufnr,
		b.werks
	ORDER BY
		ekpo.ebeln,
		ekpo.ebelp,
		ekpo.AEDAT DESC,
		ekpo.EBELN DESC) rn
	FROM
		NSDM_V_MSEG B
	LEFT JOIN ZBAR_ITEM A ON
		B.XBLNR_MKPF = A.ID
		AND B.LINE_ID = A.CANUM
	INNER JOIN ZBAR_head ON
		B.XBLNR_MKPF = ZBAR_head.ID
	LEFT JOIN ZPPT0025 ON
		A.ZREASON = ZPPT0025.ZREASON
	LEFT JOIN resb ON
		A.rsnum = resb.rsnum
		AND A.matnr = resb.matnr
		AND A.werks = resb.werks
		AND A.aufnr = resb.aufnr
		AND A.RSPOS = resb.RSPOS
	LEFT JOIN AFPO ON
		A.AUFNR = AFPO.AUFNR
	LEFT JOIN AFKO ON
		AFKO.AUFNR = AFPO.AUFNR
	LEFT JOIN makt ON
		AFKO.PLNBEZ = makt.matnr
	LEFT JOIN VBAK ON
		VBAK.VBELN = AFPO.KDAUF
	LEFT JOIN EKPO ON
		EKPO.MATNR = B.MATNR
		AND EKPO.WERKS = B.WERKS
	LEFT JOIN EKKO ON
		EKKO.EBELN = EKPO.EBELN
	WHERE
		B.BWART = 'Z61'
		AND TO_DATE(B.BUDAT_MKPF, 'YYYYMMDD') >= TO_DATE('20231101', 'YYYYMMDD')
		AND TO_DATE(B.BUDAT_MKPF, 'YYYYMMDD') < TO_DATE('20231201', 'YYYYMMDD')
		AND B.werks = '1161'
)
WHERE
	rn = 1

3 Summary

Mysql is flexible.