我们在查询数据的时候,经常遇到一对多的表关系,对其进行多表关联查询,
但是在列表里面显示数据的时候需要根据一的表进行数据显示,同时在一行内显示多的数据。大概例子如下图所示
这时候我们可以使用group_concat进行数据查询的一些处理:
SELECT
com.serial_number,
cis.customer_type,
cis.company_name,
cis.`name` ,
cis.logo_pic,
cba.contract,
cba.telephone,
cba.province,
cba.city,
cba.area,
cba.address,
group_concat(
'{"pkProduct":"' ,cpm.pk_product,'","productName":"' ,cpm.product_name,'","productLogoPic":"' ,cpm.product_logo_pic,
',"retailPrice":' ,cpm.retail_price,',"productWeight":' ,cpm.product_weight,',"catalogName":"' ,cpm.catalog_name,
'","productNum":"' ,coap.product_num,'","discountType":"' ,cpm.discount_type,'","enoughPrice":"' ,IFNULL(cpm.enough_price,"" ),
'","subPrice":"' ,IFNULL(cpm.sub_price,"" ),'","enoughNum":"' ,IFNULL(cpm.enough_num,"" ),'","giveProductNum":"' ,
IFNULL(cpm.give_product_num,"" ),'","giveProductName":"' ,IFNULL(cpm.give_product_name,"" ),'","giveProductLogoPic":"' ,
IFNULL(cpm.give_product_logo_pic,"" ),'","discountPrice":"' ,IFNULL(cpm.discount_price,"" ),'"}' ),
com.pk_invoices,
com.total_num,
com.cost_num,
com.order_status,
com.real_num,
com.pk_order,
com.billing_status,
ccb.code_type,
ccb.bankcode
FROM
cti_order_message com
INNER JOIN cti_bank_address cba ON cba.pk_bank_address = com.pk_bank_address
INNER JOIN cti_individual_customer cis ON cis.pk_customer = com.pk_customer
LEFT JOIN cti_customer_bankcode ccb ON com.pk_bank_code = ccb.pk_bankcode
LEFT JOIN cti_order_and_product coap ON com.pk_order = coap.pk_order
LEFT JOIN (
SELECT
pm.pk_product,
pm.product_name,
pm.product_logo_pic,
cpd.discount_type,
cpd.enough_price,
cpd.sub_price,
cpd.enough_num,
cpd.give_product_num,
cpd.give_product_name,
cpd.give_product_logo_pic,
cpd.discount_price,pm.product_weight,pm.retail_price,cpc.catalog_name
FROM
cti_product_message pm
LEFT JOIN (
SELECT
cpd.pk_product,
cpd.discount_type,
cpd.enough_price,
cpd.sub_price,
cpd.enough_num,
cpd.give_product_num,
cpm.product_name AS give_product_name,
cpm.product_logo_pic AS give_product_logo_pic,
cpd.discount_price
FROM
cti_product_discount cpd
LEFT JOIN cti_product_message cpm ON cpd.give_pk_product = cpm.pk_product
WHERE
cpd.dr = 0
AND cpd.start_date <= '2017-08-22 13:16:00'
AND cpd.end_date >= '2017-08-22 13:16:00'
AND cpd.use_status = 1
AND (
cpd.is_all_bank = 1
OR (
is_all_bank = 2
AND FIND_IN_SET(
'40289f9a5d7d9602015d7d9baf000000' ,
pkBank
)
)
)
) cpd ON pm.pk_product = cpd.pk_product
LEFT JOIN cti_product_catalog cpc ON cpc.pk_catalog = pm.pk_catalog
) cpm ON coap.pk_product = cpm.pk_product
WHERE
com.dr = 0
AND com.order_status = 1
AND com.pk_order = '40289f9a5def7129015def728c310003'
GROUP BY
com.pk_order;