sql - 如何仅返回加入时的最新记录

This question already has answers here:

Fetch the row which has the Max value for a column

（35个答案）


5年前关闭。





我正在参加餐桌。我只想基于日期字段从联接表中返回一条记录。

这是到目前为止我所做的简化工作：http://sqlfiddle.com/#!3/be0cdd/2

我的桌子：

  CUSTOMER

| CustomerID |
--------------
| 1          |


  PURCHASE

| PurchaseID | CustomerID | ProductID | CreateDate | ArchiveFlag |
------------------------------------------------------------------
| 1          | 1          | 443       | 01-FEB-15  | F           |
| 2          | 1          | 551       | 01-MAR-15  | F           |
| 3          | 1          | 151       | 01-JAN-15  | F           |
| 4          | 1          | 654       | 01-MAY-15  | T           |
| 5          | 1          | 345       | 01-APR-15  | T           |

这是查询本身：

select *
from customer c
join purchase p
on c.customerid = p.customerid
and p.archiveflag = 'F';

我只想为每个客户返回未归档的最新购买信息（在本示例中，购买ID为2）。

理想输出：

| CustomerID | PurchaseID | CustomerID_2 | ProductID | CreateDate | ArchiveFlag |
|--------------------------------------------------------------------------------
| 1          | 2          | 1            | 551       | 01-MAR-15  | F           |

最佳答案

Oracle 12c引入了行限制子句，您可以这样做（如果您只想要一个结果）：

SELECT *
FROM   customer c
       INNER JOIN purchase p
       ON ( c.customerid = p.customerid )
WHERE  p.archiveflag = 'F'
ORDER BY
       CreateDate DESC
FETCH FIRST 1 ROW ONLY

在早期版本中，您可以执行以下操作：

SQL Fiddle

Oracle 11g R2架构设置：

create table CUSTOMER(CustomerID INT);
create table PURCHASE(PurchaseID INT, CustomerID INT, ProductID INT, CreateDate date, ArchiveFlag char);

insert into CUSTOMER values(1);
insert into CUSTOMER values(2);

insert into PURCHASE values(1,1,443,'01-FEB-15','F');
insert into PURCHASE values(2,1,551,'01-MAR-15','F');
insert into PURCHASE values(3,1,151,'01-JAN-15','F');
insert into PURCHASE values(4,1,654,'01-MAY-15','T');
insert into PURCHASE values(5,1,345,'01-APR-15','T');
insert into PURCHASE values(6,2,234,'01-MAY-15','T');
insert into PURCHASE values(7,2,134,'01-APR-15','F');
insert into PURCHASE values(8,2,999,'01-JAN-15','F');
insert into PURCHASE values(9,2,724,'07-JUN-15','F');
insert into PURCHASE values(10,2,345,'01-JUN-15','T');

查询1-如果您只想获取单个客户的最新消息：

SELECT *
FROM   (
  SELECT *
  FROM   Purchase
  WHERE  archiveflag = 'F'
  AND    CustomerID = 1
  ORDER BY
         CreateDate DESC
)
WHERE ROWNUM = 1

Results：

| PURCHASEID | CUSTOMERID | PRODUCTID |              CREATEDATE | ARCHIVEFLAG |
|------------|------------|-----------|-------------------------|-------------|
|          2 |          1 |       551 | March, 01 2015 00:00:00 |           F |

查询2-如果您想获取所有客户的最新消息：

SELECT PurchaseID,
       CustomerID,
       ProductID,
       CreateDate,
       ArchiveFlag
FROM   (
  SELECT p.*,
         ROW_NUMBER() OVER ( PARTITION BY p.CustomerID ORDER BY CreateDate DESC ) RN
  FROM   purchase p
  WHERE  ArchiveFlag = 'F'
)
WHERE  RN = 1

Results：

| PURCHASEID | CUSTOMERID | PRODUCTID |              CREATEDATE | ARCHIVEFLAG |
|------------|------------|-----------|-------------------------|-------------|
|          2 |          1 |       551 | March, 01 2015 00:00:00 |           F |
|          9 |          2 |       724 |  June, 07 2015 00:00:00 |           F |

如果PURCHASE.CUSTOMERID是链接到CUSTOMER.CUSTOMERID的非空外键，则无需连接表（如上所述）。