PHP MySQL分组问题

Question

如果我在MySQL中有一个包含以下数据的表：

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

我需要它看起来像这样：

Company                                        CompanyEntity        OrderNumber      Delivery Date
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

我试过了：

SELECT
nextgenorder_companyname,
nextgenorder_company_entity,
nextgenorder_ordernumber,
nextgenorder_deliverydate
    FROM nextgenorders2
WHERE nextgenorder_deliverydate='2020-11-11'
GROUP BY nextgenorder_companyname,nextgenorder_company_entity
ORDER BY nextgenorder_companyname

这并没有给我想要的结果。任何帮助都将不胜感激。

SQL Fiddle

谢谢

Answer 1

您需要使用GROUP_CONCAT来聚合值，并根据示例数据按名称中的第一个单词进行分组，因此可以使用SUBSTRING_INDEX来提取该值。要将名称中可能的/作为分隔符而不是空格来处理，您可以REPLACE它们：

SELECT
    GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS Company,
    GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS CompanyEntity,
    GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS OrderNumber,
    GROUP_CONCAT(DISTINCT nextgenorder_deliverydate) AS `Delivery Date`
FROM nextgenorders2
WHERE nextgenorder_deliverydate='2020-11-11'
GROUP BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)
ORDER BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)

输出：

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

Demo on SQLFiddle