Classic sql example

1. Find all the units and corresponding barcodes of the products in the order, and combine them for output:

group_concat([DISTINCT] Field to concatenate [Order BY ASC/DESC sort field] [Separator
'separator'])

Goods table tp_goods:

Goods unit table tp_goodsunit:

Order form tp_order:

SELECT o.orderNo,o.number,o.goodsUnit,GROUP_CONCAT(DISTINCT CONCAT(gu.unit,gu.barcode) SEPARATOR '*') AS allBarcodeFROM tp_order oLEFT JOIN tp_goodsunit gu ON gu.goodsId=o.goodsIdWHERE o.id = 1GROUP BY o.orderNo,o.number,o.goodsUnit

2. According to the product unit of the order, find the corresponding barcode:

Goods table tp_goods:

Goods unit table tp_goodsunit:

Order form tp_order:

SELECT o.orderNo,o.number,o.goodsUnit,u.barcodeFROM tp_order oLEFT JOIN(SELECT gu.barcode,gu.goodsIdFROM tp_goodsunit guINNER JOIN tp_order poON gu.goodsId=po.goodsId AND gu.unit=po.goodsUnit AND po.id=1) u ON u.goodsId = o.goodsIdWHERE o.id = 1

3.sql to determine whether a field is a null value, or an empty string

LENGTH(u.barcode)>0, means not null, not empty string
LENGTH(u.barcode)<=0, means null or empty string

IF(LENGTH(u.barcode)>0,u.barcode,g.barcode) AS a //u.barcode is not null and not empty string, take u.barcode, otherwise take g.barcode