sql - 无法使用以下查询检索日期

Question

无法使用以下查询获得所需的结果

First_Name Last_Name Email          Order_Date Sale_Amt
shaun      wong      [e-mail] 5/1/2019    $5 
shaun      wong      [e-mail] 7/1/2019    $7    
shaun      wong      [e-mail] 9-1-2019    $7 
mark       young     [e-mail] 12-1-2018   $35 
mark       young     [e-mail] 22-1-2018   $45 
mark       young     [e-mail] 15-1-2018   $75 

sql，甲骨文

select *
from sampletable test1
where test1.Order_date = (
    SELECT MAX(Order_date)
    FROM sampletable test2
    where test2.First_Name = 'shaun'
  ) 
  AND test1.sale_Amt = (
    select SUM(sale_Amt)
    FROM sampletable test2
    where test2.First_Name = 'shaun'
  );

我需要获取每个订单的最新记录，并且我需要每个最新记录的所有 sale_Amt 的总和

我编写了以下查询来提取不同的记录。

select distinct * 
from OrdersTable 
where Order_date IN ( SELECT MAX(Order_date) As Last_Order_Date FROM OrdersTable GROUP BY First_name)

标签： sqloracle

Answer 1

这是否给了你正在寻找的东西？这是甲骨文...

select first_name, last_name, email, order_date , (SELECT SUM(sale_amt)  
                                    FROM sls_detail
                                    WHERE  first_name = ED.first_name and last_name = ED.last_name AND email = ED.email
                                    GROUP BY first_name, last_name, email) sale_amt
from (select ed.*, 
             row_number() over (partition by first_name, last_name,email
                               order by order_date desc
                               ) as seqnum
      from sls_detail ed
     ) ED

where seqnum = 1;