sql - Oracle SQL 健全性检查 - 加入以获取最近创建的记录

Question

我有一个设施/节点表，以及一个带有简单查询/连接的 list 表，以显示当前打开的 list (具有空的 CLOSE_DATE 字段)。

节点可能有也可能没有检查表，只有一个打开的检查表，多个关闭的检查表。相当标准的东西。

但现在我还需要显示最近创建但已关闭的 list ，并且希望在开始将其集成到实际代码库之前检查我的解决方案的健全性。

注意:Oracle SQL

CREATE TABLE test_user (user_id number, user_name varchar2(16));
CREATE TABLE test_node (node_id number, node_name varchar2(16));
CREATE TABLE test_list (list_id number, node_id number, user_id number, create_date date, close_date date);
INSERT INTO test_user VALUES (1, 'apple');
INSERT INTO test_user VALUES (2, 'pear');
INSERT INTO test_node VALUES (1, 'facility 1');
INSERT INTO test_node VALUES (2, 'facility 2');
INSERT INTO test_node VALUES (3, 'facility 3');
INSERT INTO test_node VALUES (4, 'facility 4');
INSERT INTO test_list VALUES (1, 1, 1,  '1-jan-2015', NULL);
INSERT INTO test_list VALUES (2, 2, 1, '1-jan-2015', '1-feb-2015');
INSERT INTO test_list VALUES (3, 2, 1, '1-apr-2015', '1-jun-2015');
INSERT INTO test_list VALUES (4, 2, 2, '1-mar-2015', '2-mar-2015');
INSERT INTO test_list VALUES (5, 2, 1, '1-nov-2015', NULL);
INSERT INTO test_list VALUES (6, 4, 2, '1-nov-2015', '15-nov-2015');

用户表主要是其中的一部分，以使左连接更复杂一些以模拟实际数据库。

我正在尝试创建一个 SQL，它将返回每个节点/设施、关于其当前打开的 list 的信息(如果有的话)以及关于最近关闭的 list 的信息(如果有的话)。

我最初尝试解决我的问题:

WITH list_breakout_open AS (
    SELECT  ll.list_id, ll.node_id, ll.create_date,
            ll.user_id, uu.user_name

      FROM  test_list ll
      JOIN  test_user uu ON ll.user_id = uu.user_id
     WHERE  ll.close_date IS NULL
), list_breakout_close AS (
    SELECT  ll.list_id, ll.node_id, ll.create_date, ll.close_date,
            ll.user_id, uu.user_name,
            RANK() OVER (PARTITION BY ll.node_id ORDER BY ll.create_date DESC) AS close_rank

      FROM  test_list ll
      JOIN  test_user uu ON ll.user_id = uu.user_id
     WHERE  ll.close_date IS NOT NULL
)
SELECT  nn.node_id, nn.node_name, 
        lbo.list_id AS open_list_id, lbo.user_name AS open_user_name, lbo.create_date AS open_create_date,
        lbc.list_id AS close_list_id, lbc.user_name AS close_user_name, lbc.create_date AS close_create_date, lbc.close_date AS close_close_date

  FROM  test_node nn
  LEFT JOIN list_breakout_open lbo ON nn.node_id = lbo.node_id
  LEFT JOIN list_breakout_close lbc ON nn.node_id = lbc.node_id
                                    AND close_rank = 1;

表格永远不会变得很大(几千行，而不是几百万)，所以性能不是大问题。我最关心的是代码维护，想要一个干净的查询，当有人在 5 年后查看这个烂摊子时会有意义。

我的解决方案看起来合理吗？我是否遗漏了一些明显的东西，以后可能会误导我？？

编辑:添加了关于我的最终查询应该做什么的注释。

Answer 1

您可以扫描每个表一次并使用分析函数进行聚合，如下所示:

SQL> SELECT n.node_id,
  2         n.node_name,
  3         min(case when l.close_date is null then l.list_id end) keep (dense_rank first order by nvl2(l.close_date, null, l.create_date) desc nulls last) open_list_id,
  4         min(case when l.close_date is null then u.user_name end) keep (dense_rank first order by nvl2(l.close_date, null, l.create_date) desc nulls last) open_user_name,
  5         min(case when l.close_date is null then l.create_date end) keep (dense_rank first order by nvl2(l.close_date, null, l.create_date) desc nulls last) open_create_date,
  6         min(case when l.close_date is not null then l.list_id end) keep (dense_rank first order by nvl2(l.close_date, l.create_date, null) desc nulls last) close_list_id,
  7         min(case when l.close_date is not null then u.user_name end) keep (dense_rank first order by nvl2(l.close_date, l.create_date, null) desc nulls last) close_user_name,
  8         min(case when l.close_date is not null then l.create_date end) keep (dense_rank first order by nvl2(l.close_date, l.create_date, null) desc nulls last) close_create_date,
  9         min(case when l.close_date is not null then l.close_date end) keep (dense_rank first order by nvl2(l.close_date, l.create_date, null) desc nulls last) close_close_date
 10    FROM test_node n
 11    LEFT JOIN test_list l ON l.node_id = n.node_id
 12    LEFT JOIN test_user u ON u.user_id = l.user_id
 13   GROUP BY n.node_id, n.node_name
 14   ORDER BY node_id;
   NODE_ID NODE_NAME        OPEN_LIST_ID OPEN_USER_NAME   OPEN_CREATE_DATE CLOSE_LIST_ID CLOSE_USER_NAME  CLOSE_CREATE_DATE CLOSE_CLOSE_DATE
---------- ---------------- ------------ ---------------- ---------------- ------------- ---------------- ----------------- ----------------
         1 facility 1                  1 apple            01/01/2015                                                        
         2 facility 2                  5 apple            01/11/2015                   3 apple            01/04/2015        01/06/2015
         3 facility 3                                                                                                       
         4 facility 4                                                                  6 pear             01/11/2015        15/11/2015

不完全确定它是否像其他人提到的那样是正确的输出。我使用您发布的解决方案对其进行了验证。