PHP MySQL显示来自GROUP_CONCAT的特定结果

Question

我有一张这样的桌子：

Company                                        CompanyEntity        OrderNumber       DeliveryDate 
Deeway HS,Deeway Pearl HS,Deeway Stony HS     20779,20780,1273     4608580,4608558    2020-11-11
Zaper LTD,Zaper CP LTD                           9995,4295         4630230,4607371    2020-11-11
Wilder                                             4224            6630210,7601371    2020-11-11

在同一张表上，我还有一个类似下面的列，用户可以单击并查看列出的所有序列/项目(我没有将该列添加到上面的表中，因为格式化后的内容在这篇文章中看起来不好：

         View/Edit                                      Company
     <a href="$id1">View</a>              Deeway HS,Deeway Pearl HS,Deeway Stony HS
     <a href="$id2">View</a>                     Zaper LTD,Zaper CP LTD
     <a href="$id3">View</a>                              Wilder        

Here's a SQL Fiddle

当我单击View Edit按钮时，我希望它加载另一个这样的表

  Company            OrderNumber     Date          Serial     Items
Deeway HS.             4608580     2020-11-11       SN1       Item1 
Deeway Pearl HS        4608580     2020-11-11       SN2       Item2 
Deeway Stony HS        4608558     2020-11-11       SN3       Item3 

我尝试了以下方法，但缺少记录：

 SELECT
 nextgenorder_id,
 GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
 GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity,
 GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
 nextgenorder_deliverydate,
 nextgenorder_item,
 nextgenorder_serialnumber
 FROM nextgenorders2
 WHERE 
 (nextgenorder_ordernumber='$nextgenorder_ordernumber' AND nextgenorder_deliverydate='$nextgenorder_deliverydate') 
 OR
 (nextgenorder_company_entity='$nextgenorder_company_entity' AND nextgenorder_deliverydate='$nextgenorder_deliverydate') 
 OR
 (nextgenorder_companyname='$nextgenorder_companyname' AND nextgenorder_deliverydate='$nextgenorder_deliverydate')
 OR
 (nextgenorder_shiptoadd1='$nextgenorder_shiptoadd1' AND nextgenorder_deliverydate='$nextgenorder_deliverydate')

Here's a SQL Fiddle

*附注，有时会在以后的日期分配序列号。并不总是有序列号的。

谢谢

Answer 1

您可以使用另一个where子句

 SELECT
 nextgenorder_id,
 GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
 GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity,
 GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
 nextgenorder_deliverydate,
 GROUP_CONCAT(DISTINCT nextgenorder_item) nextgenorder_item,
 GROUP_CONCAT(DISTINCT  nextgenorder_serialnumber) nextgenorder_serialnumber
 FROM nextgenorders2
 WHERE 
 (nextgenorder_companyname LIKE CONCAT(SUBSTRING_INDEX('Deeway  HS', ' ', 1),'%') AND nextgenorder_deliverydate='2020-11-11')

sqlfiddle example