mysql - 选择具有最大字段数的记录

Question

查询 1:

SELECT MaxTable.Customer,
       MaxTable.Product,
       Count(MaxTable.Product) AS CountOfProduct
FROM MaxTable
GROUP BY MaxTable.Customer, MaxTable.Product 

这将创建一个派生表，我想将其命名为 Product_count_query

查询 2:

SELECT Product_count_query.Customer,
       Max(Product_count_query.CountOfProduct) AS MaxOfCountOfProduct
FROM Product_count_query
GROUP BY Product_count_query.Customer;

这将创建一个派生表，我想将其命名为 max_customer_count

查询 3:

SELECT Product_count_query.Customer,
       Product_count_query.Product,
       Product_count_query.CountOfProduct
FROM Max_Customer_Count
INNER JOIN Product_count_query ON 
       (Max_Customer_Count.MaxOfCountOfProduct = Product_count_query.CountOfProduct) AND
       (Max_Customer_Count.Customer = Product_count_query.Customer);

我想将这三个查询合二为一以获得这样的结果

Customer Name   Products with max number

我的示例表是

Customer Product
James Keyboard
James Monitor
James Keyboard

输出表是(如果 James 买了 2 个键盘和 1 个显示器)

Customer Product Countof 产品 詹姆斯键盘 2

如果购买的产品数量相同，则应使用逗号分隔符连接产品名称和产品数量。

Answer 1

很遗憾 Mysql 不支持 ROW_NUMBER 窗口函数，所以，

试试这个

SELECT Customer,
       Group_concat(Product separator ','),
       Group_concat(CountOfProduct separator ',')
FROM   (SELECT MaxTable.Customer,
               MaxTable.Product,
               Count(MaxTable.Product) AS CountOfProduct
        FROM   MaxTable
        GROUP  BY MaxTable.Customer,
                  MaxTable.Product) a
WHERE  CountOfProduct = (SELECT Max(CountOfProduct)
                         FROM   (SELECT MaxTable.Customer,
                                        MaxTable.Product,
                                        Count(MaxTable.Product) AS CountOfProduct
                                 FROM   MaxTable
                                 GROUP  BY MaxTable.Customer,
                                           MaxTable.Product) b
                         WHERE  a.Customer = b.Customer)
GROUP  BY Customer

使用GROUP_CONCAT当有平局时连接结果