php - MySql复杂查询统计每个人的订单

Question

我有一个包含订单的表，其中注释记录中有订单创建者的名称。有销售人员3人。我想从每个销售人员那里获取统计数据。我想出了下面的查询来输出一个人的订单，它工作得很好，但如果可以在一个选择查询中获取每个销售人员的订单并在同一个表中输出，我真的很挣扎。我尝试了 Union 并在 select 中进行选择，但我想我构建的查询是错误的。下面的代码可以很好地仅输出 Adam 的订单(该人的销售数量和总销售值(value))。感谢您的任何提示。

SELECT MONTHNAME(orders.despatched) as Month, YEAR(orders.despatched) as Year, 
SUM(orders.price) as AdamSales, COUNT(orders.comment) as AdamQt FROM orders 
WHERE 
orders.comment LIKE '%Adam%' AND 
orders.status = 'despatched' AND 
orders.despatched BETWEEN '$d1' AND '$d2' 
GROUP BY YEAR(orders.despatched), MONTH(orders.despatched)
order by orders.despatched ";

我知道，如果人名不仅仅是评论记录中某处的字符串，那么按人分组可能是最好的。

Answer 1

您可以根据评论对 case 语句进行分组

SELECT 
       ( case when ORDERs.comments LIKE '%Adam%' THEN 'Adam'  
                 when ORDERS.comments LIKE '%Peter%' THEN 'Peter' END ) as 'Person'
       MONTHNAME(orders.despatched) as Month, 
       YEAR(orders.despatched) as Year, 
       SUM(orders.price) as Sales, 
       COUNT(orders.comment) as Qt FROM orders 
WHERE 
(orders.comment LIKE '%Adam%' OR orders.comment LIKE '%Peter%' ) AND
orders.status = 'despatched' AND 
orders.despatched BETWEEN '$d1' AND '$d2' 
GROUP BY YEAR(orders.despatched), MONTH(orders.despatched),
     ( case when ORDERs.comments LIKE '%Adam%' THEN 'Adam'  
                 when ORDERS.comments LIKE '%Peter%' THEN 'Peter' END )
order by orders.despatched