mysql - 如何使用 Concat 进行 GROUP_CONCAT

Question

Product name    supplier
A               Su1
A               Su1
A               Su2
B               Su1
C               Su3

我想要这样

A - su1, A-su2, B-su1, C-su3

查询:

SELECT 
vtiger_salesorder.salesorder_no,

(Select 
group_concat(DISTINCT concat(vtiger_products.productname, '-', vtiger_vendor.vendorname ) SEPARATOR ', ') 
FROM
  vtiger_salesorder
LEFT Join vtiger_inventoryproductrel ON vtiger_salesorder.salesorderid = vtiger_inventoryproductrel.id
inner Join vtiger_products ON vtiger_products.productid = vtiger_inventoryproductrel.productid
inner Join softMax_SalesOrderVendorInfo ON softMax_SalesOrderVendorInfo.salesorderid = vtiger_salesorder.salesorderid 
LEFT JOIN vtiger_vendor ON softMax_SalesOrderVendorInfo.vendorid = vtiger_vendor.vendorid
where (vtiger_salesorder.salesorderid = vtiger_inventoryproductrel.id 
AND vtiger_salesorder.salesorderid = vtiger_crmentity.crmid 
and  (softMax_SalesOrderVendorInfo.status = '0') )Group by vtiger_salesorder.salesorderid Limit 0,1) As SuName1

FROM
vtiger_salesorder
INNER JOIN vtiger_inventoryproductrel ON vtiger_salesorder.salesorderid = vtiger_inventoryproductrel.id
Inner Join vtiger_crmentity ON vtiger_salesorder.salesorderid = vtiger_crmentity.crmid
Order by vtiger_salesorder.salesorder_no

Answer 1

根据您的原始数据，您可以简单地执行以下操作:

select distinct group_concat(ProductName, '-', Supplier separator ', ')
from table t;

我不知道查询与问题有什么关系，因为您似乎已经拥有适当格式的数据。