Geek asks and answers

MySQL Intra-group aggregates without sub-queries - updated test data

I have two tables in the MySQL sales database:

Order table:

CREATE TABLE salestest.`orders` (  
`ID` int(11) unsigned NOT NULL auto_increment,  
`OrderDate` datetime NOT NULL,  
`CustomerID` int(11) unsigned NOT NULL,  
PRIMARY KEY (`ID`),  
UNIQUE KEY `ID` (`ID`),  
KEY `OrderDate` (`OrderDate`),  
KEY `CustomerID` (`CustomerID`)  
) ENGINE=InnoDB;  

INSERT INTO salestest.orders VALUES  
( 1, '2012-04-15', 1 ),  
( 2, '2012-05-20', 1 ),  
( 3, '2012-06-30', 1 );

OrderDetails table:

CREATE TABLE salestest.`OrderDetails` (  
`ID` int(11) unsigned NOT NULL auto_increment,  
`OrderID` int(11) unsigned NOT NULL,  
`ProductID` int(11) unsigned NOT NULL,  
`Price` double NOT NULL default '0',  
PRIMARY KEY  (`ID`),  
UNIQUE KEY `ID` (`ID`),  
KEY `OrderID` (`OrderID`),  
KEY `ProductID` (`ProductID`),  
CONSTRAINT `OrderID_fk` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`ID`)  
) ENGINE=InnoDB;  

INSERT INTO salestest.OrderDetails VALUES  
( 1, 1, 1, 2 ),  
( 2, 1, 2, 15 ),  
( 3, 1, 3, 22 ),  
( 4, 2, 1, 3 ),  
( 5, 2, 2, 17 ),  
( 6, 2, 3, 23 ),  
( 7, 2, 4, 40 ),  
( 8, 3, 1, 4 ),  
( 9, 3, 2, 20 );

Now I need to choose for each customer the last price they buy for each product.

An easy way to do this is to use a subquery:

SELECT od2.CustomerID,od2.ProductID, od2.Price AS LastPrice, od2.OrderDate AS LastDate  
FROM (SELECT o1.ID, o1.CustomerID, o1.OrderDate, od1.ProductID, od1.Price  
  FROM orders AS o1  
  LEFT JOIN OrderDetails as od1 ON o1.ID=od1.OrderID  
  ORDER BY OrderDate DESC) AS od2  
GROUP BY CustomerID, ProductID  
ORDER BY CustomerID, ProductID;

Result:

CustomerID ProductID LastPrice LastDate
1 1 4 2012-06-30 00:00:00
1 2 20 2012-06-30 00:00:00
1 3 23 2012-05-20 00:00:00
1 4 40 2012-05- 20 00:00:00

Now the question; how can I get the same result, if I want to avoid a subquery, temporary tables or views, I just want to use joins; this query is a small part of a much larger query, and the subquery is very inefficient.

:

SELECT o1.CustomerID, od1.ProductID, od1.Price AS LastPrice, o1.OrderDate AS LastDate
FROM Orders AS o1 LEFT JOIN OrderDetails as od1 ON o1.ID = od1.OrderID
GROUP BY CustomerID, ProductID
ORDER BY CustomerID, ProductID;

:

CustomerID ProductID LastPrice LastDate
1 1 2 2012-04-15 00:00:00
1 2 15 2012-04-15 00:00:00
1 3 22 2012-04-15 00:00:00
1 4 40 2012-05-20 00:00:00

, LastPrice LastDate . , :

CustomerID ProductID LastPrice LastDate
1 1 4 2012-06-30 00:00:00
1 2 20 2012-06-30 00:00:00

spencer: :

CustomerID ProductID LastPrice LastDate
1 3 22 2012-04-15 00:00:00
1 3 23 2012-05-20 00:00:00
1 4 40 2012-05-20 00:00:00
1 1 4 2012-06-30 00:00:00
1 2 20 2012-06-30 00:00:00

, .
?

+5
4

UPDATE:

, ( ).

, .

, . ( , " " , .

...

SELECT o1.CustomerID, d1.ProductID, d1.Price, o1.Orderdate
  FROM orders o1
  JOIN OrderDetails d1 ON d1.OrderID = o1.ID
  LEFT      
  JOIN orders o2 
    ON o1.CustomerID = o2.CustomerID
       AND o1.ID <> o2.ID
       AND (o1.OrderDate < o2.OrderDate
           OR (o1.OrderDate = o2.OrderDate AND o1.ID < o2.ID)
           )
  LEFT
  JOIN OrderDetails d2
    ON d2.OrderID = o2.ID
       AND d2.ProductID = d1.ProductId
       AND (o1.OrderDate < o2.OrderDate
           OR (o1.OrderDate = o2.OrderDate AND o1.ID < o2.ID)
           OR (o1.OrderDate = o2.OrderDate AND o1.ID = o2.ID AND d1.ID < d2.ID )
           )
 WHERE d2.ID IS NULL

a >

" " .

-

. " " ( a b). - CustomerID OrderDate, OrderDetail.

 SELECT a.CustomerID, a.ProductID, a.Price, a.Orderdate
   FROM (SELECT o1.CustomerID, d1.ProductID, d1.Price, o1.OrderDate, d1.OrderID, d1.ID
           FROM orders o1
           JOIN OrderDetails d1 ON d1.OrderID = o1.ID
        ) a
   LEFT      
   JOIN (SELECT o2.CustomerID, d2.ProductID, d2.Price, o2.OrderDate, d2.OrderID, d2.ID
           FROM orders o2
           JOIN OrderDetails d2 ON d2.OrderID = o2.ID
        ) b
     ON a.CustomerID = b.CustomerID
        AND a.ProductID = b.ProductId
        AND a.OrderID <> b.OrderID
        AND a.ID <> b.ID
        AND (a.OrderDate < b.OrderDate 
             OR (a.OrderDate = b.OrderDate AND a.OrderID < b.OrderID)
             OR (a.OrderDate = b.OrderDate AND a.OrderID = b.OrderID AND a.ID < b.ID))
  WHERE b.ID IS NULL

(CTE) , MySQL .

, , " " MySQL , MySQL.

SELECT q.CustomerID
     , q.ProductID
     , q.Price
     , q.OrderDate
  FROM (SELECT IF(p.CustomerID = @last_customerid,IF(p.ProductID = @last_productid,0,1),1) AS break
             , @last_customerid := p.CustomerID AS CustomerID
             , @last_productid := p.ProductID AS ProductID
             , p.Price
             , p.OrderDate
         FROM (SELECT @last_customerid := NULL, @last_productid := NULL) i
         JOIN ( SELECT o.CustomerID, d.ProductID, o.OrderDate, d.Price 
                  FROM orders o
                  JOIN OrderDetails d ON d.OrderID = o.ID
                 ORDER BY o.CustomerID, d.ProductID, o.OrderDate DESC
              ) p
       ) q
  WHERE q.break = 1
0

"-n--"

, - SQL, , .

, :

SELECT o.CustomerID, od.ProductID, od.Price AS LastPrice, o.OrderDate AS LastDate  
FROM OrderDetails od
LEFT JOIN orders as o ON od.OrderID = o.ID
LEFT JOIN orders as o2 ON o.CustomerID = o2.CustomerID AND o.id < o2.id
WHERE o2.id IS NULL
ORDER BY o.CustomerID, od.ProductID;

, + , .

, , ( ), , + (o2 , ). , o2 , .

, , .

, , / - !

+1
select B.*,A.Price from
(select CustomerID,ProductID,OrderDate,Price from Orders o join OrderDetails od on o.ID=od.OrderID) A 
join 
(select CustomerID,ProductID,max(OrderDate) as LastOrderDate 
from Orders o 
join OrderDetails od on o.ID=od.OrderID
group by CustomerID,ProductID) B 
on A.CustomerID=B.CustomerID and A.ProductID=B.ProductID and A.OrderDate=B.LastOrderDate
0

:

select opl.CustomerID,
    opl.ProductID,
    opl.LastDate,
    od.Price
from (
    select o.CustomerID,
        od.ProductID,
        max(o.OrderDate) as LastDate
    from Orders o
    inner join OrderDetails od on o.ID = od.OrderID
    group by o.CustomerID, od.ProductID
) opl
inner join Orders o on opl.CustomerID = o.CustomerID
    and opl.LastDate = o.OrderDate
inner join OrderDetails od on o.ID = od.OrderID
0

More articles: