oracle SQL：根据特定条件将行与前一行进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9285597/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Compare row with previous row based on a specific condition
提问by CoolArchTek
I wanted to retrieve records from a table by comparing with previous entry (for that account). Please take a look the table and data below.
我想通过与以前的条目（该帐户）进行比较来从表中检索记录。请看下面的表格和数据。
In this out put I wanted is,
在这个输出中，我想要的是，
ID_NUM DELIVERY_TYPE
100 2
101 2
102 2
Explanation: I need, 100 because it is its first occurance with DELIVERY_TYPE IS 2 (Old record has 1) 101 because it is its first occurance with DELIVERY_TYPE IS 2 (Old record has 3) 102 because there is only one entry for this ID_NUM and DELIVERY_TYPE IS 2
说明：我需要，100 因为它第一次出现在 DELIVERY_TYPE IS 2（旧记录有 1） 101 因为它第一次出现在 DELIVERY_TYPE IS 2（旧记录有 3） 102 因为这个 ID_NUM 只有一个条目并且DELIVERY_TYPE 是 2
I DON'T NEED 103 because recent DELIVERY_TYPE IS 1 even it has DELIVERY_TYPE IS 2 104 because it has two or more records with DELIVERY_TYPE IS 2
我不需要 103 因为最近的 DELIVERY_TYPE IS 1 即使它有 DELIVERY_TYPE IS 2 104 因为它有两条或更多条 DELIVERY_TYPE IS 2 的记录
Any body knows how to achieve this result?
任何机构都知道如何实现这一结果？
CREATE TABLE DEMO
(
ID_NUM NUMBER(10,0),
DELIVERY_TYPE NUMBER(2,0),
NAME VARCHAR2(100),
CREATED_DATE DATE
);
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(100, 2, TO_DATE('10-FEB-12 11:08:49 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(100, 1, TO_DATE('29-JAN-12 11:09:00 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(101, 2, TO_DATE('09-FEB-12 11:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(101, 3, TO_DATE('14-JAN-12 11:09:33 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(102, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(103, 1, TO_DATE('01-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(103, 2, TO_DATE('02-JAN-12 11:09:33 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(104, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
(ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
(104, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
采纳答案by tawman
You can use the ROW_NUMBER() function to isolate the most recent rows by partitioning on the ID_NUM and ordering by the CREATED_DATE descending. Then identify the occurrences of more than one DELIVERY_TYPE = 2 to filter the result set:
您可以使用 ROW_NUMBER() 函数通过对 ID_NUM 进行分区并按 CREATED_DATE 降序排序来隔离最近的行。然后确定出现多个 DELIVERY_TYPE = 2 来过滤结果集：
SELECT ID_NUM, DELIVERY_TYPE
FROM (SELECT ID_NUM, DELIVERY_TYPE,
ROW_NUMBER() OVER (PARTITION BY ID_NUM
ORDER BY CREATED_DATE DESC) AS RN
FROM DEMO)
WHERE RN = 1
AND DELIVERY_TYPE = 2
MINUS
SELECT ID_NUM, DELIVERY_TYPE
FROM (SELECT ID_NUM, DELIVERY_TYPE, COUNT(*) AS REC_COUNT
FROM DEMO
WHERE DELIVERY_TYPE = 2
GROUP BY ID_NUM, DELIVERY_TYPE
HAVING COUNT(*) > 1)
This will return the expected results.
这将返回预期的结果。
回答by Randy
use a LAG function.
使用 LAG 函数。
it might be easier if you post a small table of values for your example instead of (/in addition to) your insert statements.
如果您为您的示例发布一个小的值表而不是（/除了）您的插入语句，它可能会更容易。
回答by John Doyle
This query will give you your desired output for the given input though I don't fully understand your rules:
尽管我不完全了解您的规则，但此查询将为您提供给定输入所需的输出：
select ID_NUM, DELIVERY_TYPE
from ( select ID_NUM, DELIVERY_TYPE, CREATED_DATE
from DEMO
group by ID_NUM, DELIVERY_TYPE, CREATED_DATE
having count(*) = 1) CNT1
where CREATED_DATE = (select max(CREATED_DATE)
from DEMO D
where D.ID_NUM = CNT1.ID_NUM)
and DELIVERY_TYPE <> 1
order by ID_NUM, DELIVERY_TYPE, CREATED_DATE
If you expand on what happens if, say, an ID_NUMhas only one entry but it is not of DELIVERY_TYPE= 1 for example then perhaps I can update.
如果您详细说明如果 anID_NUM只有一个条目但它不是DELIVERY_TYPE= 1时会发生什么，那么也许我可以更新。
回答by Allan
The following query returns one record for each id_numwhere the last delivery_typewas 2 and the value 2 appeared in delivery_typeexactly once:
以下查询为每个记录返回一个记录，id_num其中最后一个delivery_type为 2，值 2 只出现delivery_type一次：
SELECT DISTINCT id_num, last_delivery_type
FROM (SELECT id_num,
FIRST_VALUE(delivery_type)
OVER (PARTITION BY id_num
ORDER BY created_date DESC)
AS last_delivery_type,
COUNT(CASE WHEN delivery_type = 2
THEN 2 ELSE NULL END)
OVER (PARTITION BY id_num) AS delivery_type_2_cnt
FROM demo)
WHERE last_delivery_type = 2 AND delivery_type_2_cnt = 1
