電腦領域 HKEPC Hardware - Powered by Discuz! Board

標題: How can list out the order details for each order... [打印本頁]

作者: luckiejacky 時間: 2013-8-29 16:01 標題: How can list out the order details for each order...

which has the most recent purchase date

first of all, I'd like to get a subquery that
returns something like
Customer ID          Product Name       PurchaseDateTime       NetSales
1                               Jade                         29-8-2013                      3000000
1                               Gold Nuggets       29-8-2013                      3000000
1                               Gold Bars                28-8-2013                      3000000

select c.CustomerID, p.ProductName, o.PurchaseDateTime, sum(Round(od.NetSales,2))
from customers As c
INNER JOIN orders AS o ON (c.CustomerID = o.CustomerID)
INNER JOIN order_details AS od ON (o.OrderID = od.OrderID)
INNER JOIN products AS p ON (od.ProductID = p.ProductID)
group by c.CustomerID

複製代碼

Here I can't get individual Product Name
so I can feed into the next stage

select c.CustomerID, p.ProductName, o.PurchaseDateTime, sum(Round(od.NetSales,2))
from customers As c
INNER JOIN orders AS o ON (c.CustomerID = o.CustomerID)
INNER JOIN order_details AS od ON (o.OrderID = od.OrderID)
INNER JOIN products AS p ON (od.ProductID = p.ProductID)
INNER JOIN (select c.CustomerID, max(o.PurchaseDateTime)
AS lastVisit
from customers AS c INNER JOIN
orders AS o ON (c.CustomerID = o.CustomerID)
group by c.CustomerID) AS lastTimes
ON o.PurchaseDateTime = lastTimes.lastVisit
group by c.CustomerID

複製代碼

Customer ID  Product Name  Purchase Date NetSales
1                      Jade                   29-8-2013       3000000
1                   Gold Nuggets 29-8-2013       3000000

Any help please..
Thanks

作者: henrywho 時間: 2013-9-1 13:33

simple but slow method:

....
from order
where trunc(PurchaseDateTime) in
(
select max(trunc(PurchaseDateTime)) max_pu_dt
from orders
)

複製代碼

作者: 杜龍 時間: 2013-9-1 15:43

where exists (select 1 from orders where PurchaseDateTime >=? and customerid = c.id)