Mysql-CONCAT和GROUP_CONCAT组合使用场景

拼接一个头明细信息

甚至可以直接把结果，转为实体或者xml或者json进行传输。
字符串的操作

SELECT
	XBLNR,
	BUDAT,
	BLDAT,
	ZYKNR,
	UMWRK,
	UMLGO,
	WERKS,
	LGORT,
	CONCAT(
		'[',
		GROUP_CONCAT(
			CONCAT(
				'{"MATNR":"',
				IFNULL(MATNR, ''),
				'","ZYKNN":"',
				ZYKNN,
				'","ERFME":"',
				ERFME,
				'","ERFMG":',
				IFNULL(ERFMG, 0),
				'}'
			) SEPARATOR ','
		),
		']'
	) ITEM
FROM
	(
		SELECT
			pod.purchaseOrderHeaderCode XBLNR,
			DATE_FORMAT(NOW(), '%Y%m%d') BUDAT,
			DATE_FORMAT(NOW(), '%Y%m%d') BLDAT,
			pod.sapPurchaseOrderCode ZYKNR,
			pod.sapFactory UMWRK,
			pod.sapWarehouseCode UMLGO,
			poh.sapOutFactoryCode WERKS,
			poh.sapOutWarehouseCode LGORT,
			pod.skuCode MATNR,
			pod.sapPurchaseOrderLineCode ZYKNN,
			IFNULL(pod.sapUnit, sku.orderUnit) ERFME,
			CASE IFNULL(pod.sapUnit, sku.orderUnit)
		WHEN sku.orderUnit THEN
			pod.receiptQty
		ELSE
			pod.receiptQty * IFNULL(sku.basicUnitNumerator, 1) / IFNULL(sku.basicUnitDenominator, 1)
		END ERFMG
		FROM
			purchase_order_detail pod
		INNER JOIN purchase_order_header poh ON poh.id = pod.purchaseOrderHeaderId
		INNER JOIN SKU ON sku.`code` = pod.skuCode
		AND poh.companyCode = sku.companyCode
		AND pod.receiptQty > 0
		WHERE
			pod.purchaseOrderHeaderId =:id
	) A
GROUP BY
	XBLNR,
	ZYKNR,
	UMWRK,
	UMLGO

SELECT
	warehouseCode,
	companyCode,
	fitemCode,
	CONCAT(
		'[',
		GROUP_CONCAT(
			CONCAT(
				'{"warehouseCode":"',
				IFNULL(warehouseCode, ''),
				'","companyCode":"',
				companyCode,
				'","itemCode":"',
				itemCode,
				'","qtyNeededPerItem":',
				IFNULL(qtyNeededPerItem, 0),
				
				',"buildSequence":',
				IFNULL(buildSequence, 0),
				'}'
			) SEPARATOR ','
		),
		']'
	) ITEM
FROM
	(
		SELECT bh.warehouseCode as warehouseCode,
		bh.companyCode as companyCode,
		bh.itemCode as fitemCode,
		bd.itemCode as itemCode,
		bd.qtyNeededPerItem AS qtyNeededPerItem,
		BD.buildSequence AS buildSequence  FROM bom_header bh  INNER JOIN bom_detail bd on bh.id=bd.bomId WHERE 1=2
	) A
GROUP BY
	warehouseCode,
	companyCode,
	fitemCode

[{"warehouseCode":"1111","companyCode":"111","itemCode":"010101000113","qtyNeededPerItem":4,"buildSequence":1}]