66个字段，11张表，n个嵌套子查询，281行sql

最新推荐文章于 2025-06-03 11:51:41 发布

Oliver_Liuqifan

最新推荐文章于 2025-06-03 11:51:41 发布

阅读量122

点赞数

文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/Oliver_Liuqifan/article/details/133168399

版权

博客记录了一个复杂的ERP数据展示需求，涉及66个字段、11张表、n个嵌套子查询和281行SQL。还详细介绍了SQL中返回行号的函数，如RANK()、DENSE_RANK()，以及返回数据条数和处理分组字段的函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录一个逆天的需求，到底是什么客户打算把erp当做excel用？要求在页面上一行信息展示六十多个字段，几乎是全量展示，所涉及的数据如题66个字段，11张表，n个嵌套子查询，281行sql，以及多入牛毛的sql函数

以下贴上这条sql的完整代码

SELECT ROW_NUMBER
	( ) OVER ( PARTITION BY PO_NUMBER ORDER BY UPDATE_END_OF_PRODUCTION_DATE ) ROWID,           * 
FROM
	(
	SELECT A
		.END_ITEM_CODE,
		SUM ( CAST ( A.QTY AS INTEGER ) ) AS QTY,
		SUM ( CAST ( A.PRICE AS DECIMAL ( 10, 2 ) ) ) AS PRICE,
		SUM ( CAST ( A.QTY_DELIVERABLE AS INTEGER ) ) AS QTY_DELIVERABLE,
		MAX ( CAST ( A.ORDER_QUANTITY AS DECIMAL ( 10, 2 ) ) ) AS ORDER_QUANTITY,
		MAX ( A.DELIVERY_ID ) AS DELIVERY_ID,
		MAX ( A.ORDER_FOLLOW_UP_ID ) AS ORDER_FOLLOW_UP_ID,
		MAX ( A.SUB_INSPECTION_ID ) AS SUB_INSPECTION_ID,
		MAX ( A.SUB_PRODUCT_FINISHED_ID ) AS SUB_PRODUCT_FINISHED_ID,
		MAX ( A.PO_NUMBER ) AS PO_NUMBER,
		MAX ( A.CUSTOMER ) AS CUSTOMER,
		MAX ( A.FINAL_CUSTOMER ) AS FINAL_CUSTOMER,
		MAX ( A.SUPPLIER ) AS SUPPLIER,
		MAX ( A.ORDER_DATE ) AS ORDER_DATE,
		MAX ( A.CATEGORY ) AS CATEGORY,
		MAX ( A.REQUEST_END_OF_PROD_DATE ) AS REQUEST_END_OF_PROD_DATE,
		MAX ( A.CONTRACT_END_OF_PROD_DATE ) AS CONTRACT_END_OF_PROD_DATE,
		MAX ( A.UPDATE_END_OF_PRODUCTION_DATE ) AS UPDATE_END_OF_PRODUCTION_DATE,
		MAX ( A.INSPECTION_RESULT ) AS INSPECTION_RESULT,
		MAX ( A.INSPECTION_DATE ) AS INSPECTION_DATE,
		MAX ( A.ITEM_CODE ) AS ITEM_CODE,
		MAX ( A.QC_REPORT_VALIDATION ) AS QC_REPORT_VALIDATION,
		MAX ( A.UNIQUE_ID ) AS UNIQUE_ID,
		MAX ( A.DOCUMNET_STATUS ) AS DOCUMENT_STATUS,
		MAX ( A.priority ) AS priority,
		MAX ( A.shipment_order_status ) AS shipment_order_status,
		MAX ( A.production_status ) AS production_status,
		MAX ( A.inspection_status ) AS inspection_status,
		MAX ( A.custom_inspection ) AS custom_inspection,
		MAX ( A.custom_instruction ) AS custom_instruction,
		MAX ( A.number_of_ref_in_the_tc ) AS number_of_ref_in_the_tc,
		MAX ( A.etd ) AS etd,
		MAX ( A.atd ) AS atd,
		MAX ( A.eta ) AS eta,
		MAX ( A.tracking_number_of_original_document ) AS tracking_number_of_original_document,
		MAX ( A.transport_to_delivery_address ) AS transport_to_delivery_address,
		MAX ( A.actual_delivery_date ) AS actual_delivery_date,
		MAX ( A.comments ) AS comments,
		MAX ( A.invoicing_to_prodex_month ) AS invoicing_to_prodex_month,
		MAX ( A.amount_deposit_payment ) AS amount_deposit_payment,
		MAX ( A.deposit_payment_date ) AS deposit_payment_date,
		MAX ( A.amount_balance_or_total_payment ) AS amount_balance_or_total_payment,
		MAX ( A.balance_or_total_payment_date ) AS balance_or_total_payment_date,
		MAX ( A.teus ) AS teus,
		MAX ( A.volume_in_m_3 ) AS volume_in_m_3,
		MAX ( A.total_volume ) AS total_volume,
		MAX ( A.gross_weight ) AS gross_weight,
		MAX ( A.pallet_number ) AS pallet_number,
		MAX ( A.weight_in_kg ) AS weight_in_kg,
		MAX ( A.number_of_cartons_pallets ) AS number_of_cartons_pallets,
		MAX ( A.forwarder ) AS forwarder,
		MAX ( A.end_production_on_time ) AS end_production_on_time,
		MAX ( A.is_new_item ) AS is_new_item,
		MAX ( A.INCOTERM ) AS INCOTERM,
		MAX ( A.loading_port ) AS loading_port,
		MAX ( A.discharge_port ) AS discharge_port,
		MAX ( A.transport_mode ) AS transport_mode,
		MAX ( A.total_count ) AS total_count,
		string_agg ( DISTINCT container_number, ' ; ' ORDER BY container_number ) AS container_number,
	MAX ( A.country_of_origin ) AS country_of_origin,
	MAX ( A.brand ) AS brand,
	CASE
			
			WHEN MAX ( A.product_taxonomy_c ) IS NOT NULL THEN
			MAX ( A.product_taxonomy_c ) 
			WHEN MAX ( A.product_taxonomy_c ) IS NULL 
			AND MAX ( A.product_taxonomy ) IS NOT NULL THEN
				string_agg ( DISTINCT product_taxonomy, ' ; ' ORDER BY product_taxonomy ) ELSE NULL 
			END AS product_taxonomy,
		MAX ( A.on_time ) AS on_time,
		MAX ( A.container_type ) AS container_type,
		MAX ( A.inspection_start_date ) AS inspection_start_date,
		MAX ( A.lot_no ) AS lot_no,
		MAX ( A.MANUAL_GOOD_READY_FOR_PICK_UP_DATE ) AS MANUAL_GOOD_READY_FOR_PICK_UP_DATE,
		COUNT ( 1 ) OVER ( PARTITION BY 1 ) COUNT 
		FROM
			(
			SELECT
				B.END_ITEM_CODE,
				B.QTY,
				B.price,
				B.QTY_DELIVERABLE,
				B.ORDER_QUANTITY,
				B.DELIVERY_ID,
				B.ORDER_FOLLOW_UP_ID,
				B.SUB_INSPECTION_ID,
				B.SUB_PRODUCT_FINISHED_ID,
				B.PO_NUMBER,
				B.CUSTOMER,
				B.FINAL_CUSTOMER,
				B.SUPPLIER,
				B.ORDER_DATE,
				B.CATEGORY,
				B.REQUEST_END_OF_PROD_DATE,
				B.CONTRACT_END_OF_PROD_DATE,
				B.UPDATE_END_OF_PRODUCTION_DATE,
				B.INSPECTION_RESULT,
				B.INSPECTION_DATE,
				B.ITEM_CODE,
				B.QC_REPORT_VALIDATION,
				B.UNIQUE_ID,
				B.IS_NEW_ITEM,
				B.INCOTERM,
				B.loading_port,
				B.discharge_port,
				B.transport_mode,
				B.container_number,
				B.country_of_origin,
				B.brand,
				cou.product_taxonomy AS product_taxonomy_c,
				B.product_taxonomy,
				B.on_time,
				B.inspection_start_date,
				B.lot_no,
				cou.priority,
				cou.shipment_order_status,
				cou.production_status,
				cou.inspection_status,
				cou.DOCUMNET_STATUS,
				cou.custom_inspection,
				cou.custom_instruction,
				cou.number_of_ref_in_the_tc,
				cou.etd,
				cou.atd,
				cou.eta,
				cou.tracking_number_of_original_document,
				cou.transport_to_delivery_address,
				cou.actual_delivery_date,
				cou.comments,
				cou.invoicing_to_prodex_month,
				cou.amount_deposit_payment,
				cou.deposit_payment_date,
				cou.amount_balance_or_total_payment,
				cou.balance_or_total_payment_date,
				fb.teus,
				fb.gross_weight,
				fb.total_volume,
				fb.pallet_number,
				cou.volume_in_m_3,
				cou.weight_in_kg,
				cou.number_of_cartons_pallets,
				fb.forwarder,
				cou.end_production_on_time,
				fb.container_type,
				( SELECT COUNT ( 1 ) FROM Sub_ORDER_FOLLOW_UP WHERE PARENT_KINGDEE_ID = B.KINGDEE_ID ) AS total_count,
			CASE
					
					WHEN M.MANUAL_GOOD_READY_FOR_PICK_UP_DATE IS NULL THEN
					CAST ( B.GOOD_READY_FOR_PICK_UP_DATE AS TIMESTAMP ) 
					WHEN cou.good_ready_for_pick_up_date IS NOT NULL THEN
					CAST ( cou.good_ready_for_pick_up_date AS TIMESTAMP ) ELSE M.MANUAL_GOOD_READY_FOR_PICK_UP_DATE 
				END AS MANUAL_GOOD_READY_FOR_PICK_UP_DATE 
			FROM
				(
				SELECT
				CASE
						
					WHEN
						SPF.ITEM_CODE IS NULL THEN
							OFU.PO_NUMBER || '-' || SOFU.QTY || SOFU.PRICE ELSE OFU.PO_NUMBER || '-' || SPF.ITEM_CODE || '-' || SPF.QTY || '-' || SOFU.PRICE 
							END AS UNIQUE_ID,
					CASE
							
							WHEN SI.ID IS NULL THEN
							SOFU.ID || '-' || SOFU.ITEM_CODE ELSE SOFU.ITEM_CODE || '-' || SI.ID 
						END AS SUB_INSPECTION_ID_1,
					CASE
							
							WHEN SI.QC_REPORT_VALIDATION IS NOT NULL THEN
							TO_CHAR( SI.QC_REPORT_VALIDATION + INTERVAL '3' DAY, 'YYYY-MM-DD HH24:MI:SS' ) 
							WHEN isp.inspection_end_date IS NOT NULL THEN
							TO_CHAR( isp.inspection_end_date + INTERVAL '5' DAY, 'YYYY-MM-DD HH24:MI:SS' ) 
							WHEN SPF.UPDATED_END_OF_PRODUCTION_DATE IS NOT NULL THEN
							TO_CHAR( SPF.UPDATED_END_OF_PRODUCTION_DATE + INTERVAL '10' DAY, 'YYYY-MM-DD HH24:MI:SS' ) 
							WHEN OFU.CONTRACT_END_OF_PROD_DATE IS NOT NULL THEN
							TO_CHAR( OFU.CONTRACT_END_OF_PROD_DATE + INTERVAL '10' DAY, 'YYYY-MM-DD HH24:MI:SS' ) ELSE NULL 
						END AS GOOD_READY_FOR_PICK_UP_DATE,
					CASE
							
							WHEN SI.update_end_of_production_date IS NOT NULL 
							AND TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) >= ( SELECT MAX ( si.UPDATE_END_OF_PRODUCTION_DATE ) AS A FROM sub_inspection si WHERE parent_kingdee_id = isp.kingdee_id GROUP BY parent_kingdee_id ) THEN
								'On Time' 
								WHEN SI.update_end_of_production_date IS NOT NULL 
								AND ( SELECT MAX ( si.UPDATE_END_OF_PRODUCTION_DATE ) AS A FROM sub_inspection si WHERE parent_kingdee_id = isp.kingdee_id GROUP BY parent_kingdee_id ) - TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) > INTERVAL '15' DAY THEN
									'Very Late' 
									WHEN SI.update_end_of_production_date IS NOT NULL 
									AND ( SELECT MAX ( si.UPDATE_END_OF_PRODUCTION_DATE ) AS A FROM sub_inspection si WHERE parent_kingdee_id = isp.kingdee_id GROUP BY parent_kingdee_id ) > TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) 
									AND ( SELECT MAX ( si.UPDATE_END_OF_PRODUCTION_DATE ) AS A FROM sub_inspection si WHERE parent_kingdee_id = isp.kingdee_id GROUP BY parent_kingdee_id ) - TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) < INTERVAL '15' DAY THEN
										'Late' 
										WHEN SI.update_end_of_production_date IS NULL 
										AND ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) IS NOT NULL 
										AND TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) >= ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) THEN
											'On Time' 
											WHEN SI.update_end_of_production_date IS NULL 
											AND ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) IS NOT NULL 
											AND ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) - TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) > INTERVAL '15' DAY THEN
												'Very Late' 
												WHEN SI.update_end_of_production_date IS NULL 
												AND ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) IS NOT NULL 
												AND ( SELECT MAX ( spf.updated_end_of_production_date ) AS A FROM sub_product_finished spf WHERE parent_kingdee_id = pf.kingdee_id GROUP BY parent_kingdee_id ) - TO_TIMESTAMP( TO_CHAR( ofu.contract_end_of_prod_date, 'YYYY-MM-DD HH24:MI:SS' ), 'yyyy-MM-dd hh24:mi:ss' ) < INTERVAL '15' DAY THEN
													'Late' 
												END AS on_time,
												SSD.LOT_NO,
												SOFU.QTY ORDER_QUANTITY,
											CASE
													
													WHEN SD.ID IS NOT NULL THEN
													OFU.kingdee_id || '-' || SD.ID 
													WHEN SD.ID IS NULL 
													AND SPF.parent_kingdee_id IS NOT NULL THEN
														OFU.kingdee_id || '-' || SPF.parent_kingdee_id ELSE OFU.kingdee_id 
														END AS END_ITEM_CODE,
													SOFU.PRICE,
													SPF.QTY,
													SSD.QTY_DELIVERABLE,
													SSD.CONTAINER_NUMBER,
													SD.ID AS DELIVERY_ID,
													SD.INCOTERM AS INCOTERM,
													SD.loading_port,
													SD.discharge_port,
													SD.transport_mode,
													OFU.ID AS ORDER_FOLLOW_UP_ID,
													SI.ID AS SUB_INSPECTION_ID,
													SPF.ID AS SUB_PRODUCT_FINISHED_ID,
													OFU.COUNTRY_OF_ORIGIN,
													OFU.brand,
													OFU.PO_NUMBER,
													OFU.CUSTOMER,
													OFU.FINAL_CUSTOMER,
													OFU.SUPPLIER,
													OFU.ORDER_DATE,
													OFU.CATEGORY,
													OFU.REQUEST_END_OF_PROD_DATE,
													OFU.CONTRACT_END_OF_PROD_DATE,
													OFU.kingdee_id,
													SOFU.IS_NEW_ITEM,
													SOFU.product_taxonomy,
												CASE
														
														WHEN SI.UPDATE_END_OF_PRODUCTION_DATE IS NULL THEN
														SPF.UPDATED_END_OF_PRODUCTION_DATE ELSE SI.UPDATE_END_OF_PRODUCTION_DATE 
													END AS UPDATE_END_OF_PRODUCTION_DATE,
													SI.INSPECTION_RESULT,
													SI.INSPECTION_DATE,
													isp.inspection_start_date,
													SPF.ITEM_CODE,
													SI.QC_REPORT_VALIDATION 
												FROM
													SUB_ORDER_FOLLOW_UP SOFU
													INNER JOIN ORDER_FOLLOW_UP OFU ON SOFU.PARENT_KINGDEE_ID = OFU.KINGDEE_ID
													LEFT JOIN SUB_PRODUCT_FINISHED SPF ON SPF.PRE_KINGDEE_ID = SOFU.KINGDEE_ID
													LEFT JOIN product_finished pf ON spf.parent_kingdee_id = pf.kingdee_id
													LEFT JOIN SUB_INSPECTION SI ON SI.PRE_KINGDEE_ID = SPF.KINGDEE_ID
													LEFT JOIN inspection isp ON si.parent_kingdee_id = isp.kingdee_id
													LEFT JOIN SUB_SALES_DELIVERY SSD ON SI.LOT_NO = SSD.LOT_NO
													LEFT JOIN SALES_DELIVERY SD ON SD.KINGDEE_ID = SSD.PARENT_KINGDEE_ID 
												WHERE
													(
														( SOFU.CLOSING_BUSINESS != 'A' OR SOFU.CLOSING_BUSINESS IS NULL ) 
														OR ( OFU.CLOSING_STATUS != 'B' OR OFU.CLOSING_STATUS IS NULL ) 
													) 
													AND ( SI.IS_RETURN IS NULL OR SI.IS_RETURN != 'Y' OR SI.INSPECTION_RESULT IS NULL OR SI.INSPECTION_RESULT != 'Fail' ) 
												) B
												LEFT JOIN tb_manual M ON B.SUB_INSPECTION_ID_1 = M.sub_inspection_id
												LEFT JOIN client_order_follow_up cou ON B.END_ITEM_CODE = cou.order_number
												LEFT JOIN forwarder_booking fb ON B.po_number = ANY ( string_to_array( fb.po_number, ',' ) ) 
											) A 
										WHERE
											1 = 1 
											AND A.CATEGORY IN ( 'Construction', 'Industry', 'PPE', 'Steel' ) 
										GROUP BY
											A.END_ITEM_CODE 
										ORDER BY
											PO_NUMBER 
										) T 
	LIMIT 30 OFFSET 0

我一个点一个点的跟大家讲这里面涉及的函数第一个

 ROW_NUMBER
	( ) OVER ( PARTITION BY PO_NUMBER ORDER BY UPDATE_END_OF_PRODUCTION_DATE ) ROWID,

这是一个返回行号的函数，rowId即为1,2,3等等。这个函数中表示先用 PARTITION BY 后的字段分组，而后再用 ORDER BY后的字段排序，比如有三条数据PO_NUMBER都为666，date为9月23，9月24，9月25。那么返回的行号则会依次为1,2,3。

由此延伸出返回行号的另外两个函数

RANK() OVER ( PARTITION BY PO_NUMBER ORDER BY UPDATE_END_OF_PRODUCTION_DATE ) ROWID

例：三条数据，PO_NUMBER都为666，date为9月23，9月23，9月24，那么返回的rowId则会为1,1,3

DENSE_RANK() OVER ( PARTITION BY PO_NUMBER ORDER BY UPDATE_END_OF_PRODUCTION_DATE ) ROWID

例：三条数据，PO_NUMBER都为666，date为9月23，9月23，9月24，那么返回的rowId则会为1,1,2

相信大家已经看出差别了，一个是当排序字段相同时候走的跳跃排序（rank），一个是当排序字段相同时候走的连续排序（dense_rank）。这个函数后面跟的值是一样的，都是PARITITION xxx ORDER BY xxx

下一个函数

COUNT ( 1 ) OVER ( PARTITION BY 1 ) COUNT

这个是用来返回，当前返回的数据的条数的字段，比如总共返回了5条，那么，这个字段就会加在每条数据的最后一个字段里，可以用来做前端的分页记录

再下一个

string_agg ( DISTINCT container_number, ' ; ' ORDER BY container_number )

这个函数很有意思，当在嵌套查询中，下方嵌套的sql中包含group by条件时候，这时会返回goup by后的多条数据，这个函数就会帮我们拼出我们想要的那个字段，比如实例中就是把分过组的container_number去重，并用分号“；”隔开