如何选择最后的数据?Oracle

Question

列出Employees * * Invoice.

<>之前员工 : 发票:Id_emp名称id_发票id_发票日期------------------------------- ------------------------------彼得前书5:5 01.01.2014 10:562阿尔弗雷德6 6 02.04.2014 11:213杰克7 7 2014年3月9日12:322 Alfred 8 8 10.10.2014 16:43之前

如何获得所有员工和他们唯一的最后发票，即以以下形式:

<>之前Id_emp名称id_invoice日期_invoice------------------------------------------------------彼得前书5:01 . 1 2014 10:563杰克2014年3月9日12:322 Alfred 8 10.10.2014 16:43之前

I tried to do:

SELECT id_emp, name, emp.id_invoice, max(date_invoice) as date_invoice 
   FROM Employees emp, Invoice inv 
     WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name, emp.id_invoice;

Answer 1

下面的查询将帮助您。还需要对id_invoice进行分组。

SELECT id_emp, name, MAX(emp.id_invoice) AS id_invoice, 
  max(date_invoice) as date_invoice 
FROM Employees emp, Invoice inv 
WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name;

Answer 2

当您执行GROUP BY并且需要包含来自"最后"发票的更多列时，KEEP语法可以非常方便:

with employees as (
   select 1 id_emp, 'Peter' name, 5 id_invoice from dual
   union all
   select 2, 'Alfred', 6 from dual
   union all
   select 3, 'Jack', 7 from dual
   union all
   select 2, 'Alfred', 8 from dual
), invoice as (
   select 5 id_invoice, to_date('01.01.2014 10:56','DD-MM-YYYY HH24:MI') date_invoice from dual
   union all
   select 6, to_date('02.04.2014 11:21','DD-MM-YYYY HH24:MI')from dual
   union all
   select 7, to_date('03.09.2014 12:32','DD-MM-YYYY HH24:MI')from dual
   union all
   select 8, to_date('10.10.2014 16:43','DD-MM-YYYY HH24:MI')from dual
)
select emp.id_emp
     , max(emp.name) name
     , max(inv.id_invoice) keep (dense_rank last order by inv.date_invoice) last_id_invoice
     , max(inv.date_invoice) last_date_invoice
  from employees emp
  join invoice inv
      on inv.id_invoice = emp.id_invoice
 group by emp.id_emp
 order by emp.id_emp

按id_emp分组。(我假设id_emp是主键，而emp.name是多余的，因此我使用max(name) -如果id_emp不是键，则将其包含在组中。)

我们使用普通max()函数获得的最后一个发票日期。获取具有最后日期的发票的id_invoice是通过KEEP语法完成的-使用DENSE_RANK last order通过inv.date_invoice告诉max(inv.id_invoice)函数它应该只取具有last date_invoice的那些行的max()。

另一种方法是使用解析函数，例如:

with employees as (
   select 1 id_emp, 'Peter' name, 5 id_invoice from dual
   union all
   select 2, 'Alfred', 6 from dual
   union all
   select 3, 'Jack', 7 from dual
   union all
   select 2, 'Alfred', 8 from dual
), invoice as (
   select 5 id_invoice, to_date('01.01.2014 10:56','DD-MM-YYYY HH24:MI') date_invoice from dual
   union all
   select 6, to_date('02.04.2014 11:21','DD-MM-YYYY HH24:MI')from dual
   union all
   select 7, to_date('03.09.2014 12:32','DD-MM-YYYY HH24:MI')from dual
   union all
   select 8, to_date('10.10.2014 16:43','DD-MM-YYYY HH24:MI')from dual
)
select id_emp, name, id_invoice, date_invoice
  from (
   select emp.id_emp
        , emp.name
        , inv.id_invoice
        , inv.date_invoice
        , row_number() over (
             partition by emp.id_emp
             order by inv.date_invoice desc
          ) rn
     from employees emp
     join invoice inv
         on inv.id_invoice = emp.id_invoice
  )
 where rn = 1
 order by id_emp

这两种方法的优点都是不能多次访问表。第二种方法使用解析的row_number()函数是最简单的，如果您需要从"最后"行开始的许多列——在这种情况下，KEEP方法需要在许多列中复制相同的KEEP子句。

Answer 3

给你

    SELECT id_emp, name, emp.id_invoice, max(date_invoice) as date_invoice 
       FROM Employees emp , Invoice inv 
    WHERE 
emp.id_emp = inv.id_emp 
AND inv_id_invoice = (
      SELECT MAX(id_invoice) from Invoices c where c.id_emp = inv.id_emp
)

Answer 4

您需要获取最新的发票编号而不是最新的发票日期，因为同一天可以有两张发票

根据你们的数据，你们可以根据发票号来识别最新的发票。试试这个:-

SELECT id_emp, name, max(emp.id_invoice), date_invoice
FROM Employees emp, Invoice inv 
WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name, date_invoice;

Answer 5

一种方法是对发票进行rank:

SELECT id_emp, name, id_invoice
FROM   (SELECT id_emp, name, emp.id_invoice, 
               RANK()  (ORDER BY date_invoice PARTITION BY id_emp) AS rk
        FROM   Employees emp
        JOIN   Invoice inv ON emp.id_invoice = inv.id_invoice)
WHERE  rk = 1