涉及3个表的计数的SQL max

I have one table Customers with CustomerID and PhoneNumber, the second table is Orders that has CustomerId and OrderNumber and a third table OrderDetails that has OrderNumber, PriceOfOneUnit and UnitsOrdered. I need to find the PhoneNumber for the customer who placed the largest order (PriceOfOneUnit * UnitsOrdered). Doing a count(PriceOfOneUnit*UnitsOrdered) as A1 and then `Group By CustomerId Order By A1 DESC LIMIT 1 is evidently not working after joining the 3 tables. Can anyone help.

我有一个表CustomerID和PhoneNumber,第二个表是具有CustomerId和OrderNumber的订单,第三个表OrderDetails具有OrderNumber,PriceOfOneUnit和UnitsOrdered。我需要为放置最大订单(PriceOfOneUnit * UnitsOrdered)的客户找到PhoneNumber。将计数(PriceOfOneUnit * UnitsOrdered)作为A1,然后`Group By CustomerId Order by A1 DESC LIMIT 1在加入3个表后显然不起作用。谁能帮忙。

2 个解决方案

#1

If we take you at your word, and what you want is the biggest single line-item rather than the largest order, you can find the largest line-item and then find the order to which it belongs and then the customer who placed that order. You can use a dummy aggregate function to pull back the order id from orderDetails.

如果我们接受你的话,你想要的是最大的单一项目而不是最大的订单,你可以找到最大的项目,然后找到它所属的订单,然后是下订单的客户。您可以使用虚拟聚合函数从orderDetails中撤回订单ID。

EDIT: OK, for someone just starting out, I think it can be clearer to think in terms of Venn diagrams and use what are called Inline Views and subqueries:

编辑:好的,对于刚开始的人,我认为用维恩图来思考并使用所谓的内联视图和子查询可以更清楚:

select customername, phone
from customer
inner join

(

     select o.id, customerid from orders o
     inner join
      ( 
        select od.orderid from orderdetail od
        where (od.qty * od.itemprice) =
            (
              select max(od.qty * od.itemprice)
              from orderdetail as od
            ) 

      ) as biggestorder
    on o.id = biggestorder.orderid

 ) as X
on customer.id = X.customerid

Each of the queries inside parentheses returns a set that can be joined/intersected with other sets.

括号内的每个查询都返回一个可以与其他集合连接/相交的集合。

#2

Give this a try,

尝试一下,

SELECT  cus.CustomerId, cus.PhoneNumber
FROM    Customers cus
        INNER JOIN Orders a 
            ON cus.CustomerId = a.CustomerId
        INNER JOIN OrderDetails b
            On a.OrderNumber  = b.OrderNumber 
GROUP   BY cus.CustomerId, cus.PhoneNumber
HAVING  SUM(b.PriceOfOneUnit * b.UnitsOrdered) = 
        (
            SELECT  SUM(b.PriceOfOneUnit * b.UnitsOrdered) totalOrdersAmount
            FROM    Orders aa
                    INNER JOIN OrderDetails bb
                        On aa.OrderNumber  = bb.OrderNumber
            GROUP   BY aa.CustomerId
            ORDER   BY totalOrdersAmount DESC
            LIMIT   1
        )

#1