我有表名位置,它包含三个字段。我需要根据 last_update_date 找到特定人员 id 的最新两行,并比较这两个最新行的location_id,如果两个location_id相同,则返回 true,否则返回 false。
我尝试使用 join 编写查询,但似乎没有解决我的问题。
表代码段。代码段 1:
Locations
person_id last_update_date location_id
368 21/10/08 14:37 234
368 28-02-00 06:18:12.000000000 AM 431
368 11-02-98 03:56:33.000000000 AM 431
368 13-01-98 08:50:37.000000000 AM 212
片段2:
person_id last_update_date location_id
368 21/10/08 14:37 null
368 28-02-00 06:18:12.000000000 AM 431
368 11-02-98 03:56:33.000000000 AM 431
368 13-01-98 08:50:37.000000000 AM 212
片段3:
person_id last_update_date location_id
368 21/10/08 14:37 431
368 28-02-00 06:18:12.000000000 AM 431
368 11-02-98 03:56:33.000000000 AM 431
368 13-01-98 08:50:37.000000000 AM 212
片段4:
person_id last_update_date location_id
368 21/10/08 14:37 431
对于上表片段 1,人员 ID 368 的两个最新行是前两行,其location_id不同,因此我想在这种情况下返回 false。请帮助我进行相同的预言机查询。代码段 2,查询应返回 false。代码段 3,查询应返回 true。Snippet4,查询应返回 false,因为没有其他行具有相同的person_id进行比较。
这是一个选项; test
CTE 是您已有的,有用的代码从第 #7 行开始。
SQL> with locations (person_id, last_update_date, location_id) as
2 (select 368, to_date('21.10.2008 14:37', 'dd.mm.yyyy hh24:mi'), 234 from dual union all
3 select 368, to_date('28.02.2000 06:18', 'dd.mm.yyyy hh24:mi'), 431 from dual union all
4 select 368, to_date('11.02.1998 03:56', 'dd.mm.yyyy hh24:mi'), 431 from dual union all
5 select 368, to_date('13.01.1998 08:50', 'dd.mm.yyyy hh24:mi'), 212 from dual
6 ),
7 temp as
8 (select person_id, last_update_date, nvl(location_id, -1) location_id,
9 row_number() over (partition by person_id order by last_update_date desc) rnd
10 from locations
11 )
12 select person_id,
13 min(last_update_date) min_date,
14 max(last_update_date) max_date,
15 case when min(location_id) <> max(location_id) or count(*) = 1 then 'false'
16 else 'true'
17 end flag
18 from temp
19 where rnd <= 2
20 group by person_id;
PERSON_ID MIN_DATE MAX_DATE FLAG
---------- ---------- ---------- -----
368 28.02.2000 21.10.2008 false
SQL>
[编辑:如何使用CTE]
CTE(公用表表达式,或带因式分解子句(有其语法。你不能自己发明。
不像你用的:
(SELECT person_id,
last_update_date,
location_id,
ROW_NUMBER ()
OVER (PARTITION BY person_id ORDER BY last_update_date DESC)
rnd
FROM locations)
as temp, select pid, ...
但
WITH temp
AS (SELECT person_id,
last_update_date,
location_id,
ROW_NUMBER ()
OVER (PARTITION BY person_id ORDER BY last_update_date DESC)
rnd
FROM locations)
SELECT pid, ...