Oracle:左连接非常大的表并将连接的行限制为最大字段值的行 [英] Oracle: Left join very big table and limit the joined rows to one with the largest field value

问题描述

我有两个桌子.第二个通过m_id引用第一个.

I have two tables. The second one references to the first one by m_id.

主表

M_ID | M_FIELD
  1  | 'main1'
  2  | 'main2'
  3  | 'main3'

子表

S_ID | S_FIELD  | S_ORDER | M_ID
  1  | 'sub1-1' |    1    |  1
  2  | 'sub1-2' |    2    |  1
  3  | 'sub1-3' |    3    |  1
  4  | 'sub2-1' |    1    |  2
  5  | 'sub2-2' |    2    |  2
  6  | 'sub2-3' |    3    |  2
  7  | 'sub3-1' |    1    |  3
  8  | 'sub3-2' |    2    |  3
  9  | 'sub3-3' |    3    |  3

我需要连接这两个表(按M_ID)，但是从Sub-table中，我只需要具有最大值S_ORDER的行.

I need to join these two tables (by M_ID) but from the Sub-table I need only the row with the largest value of S_ORDER.

因此查询的预期结果是:

So the expected result of the query is:

M_ID | M_FIELD | S_FIELD
  1  | 'main1' | 'sub1-3'
  2  | 'main2' | 'sub2-3'
  3  | 'main3' | 'sub3-3'

此问题的答案中有一个具有解析功能的可行解决方案:

There is working solution with analytical function in the answer of this question: How do I limit the number of rows returned by this LEFT JOIN to one? (I will post it at the bottom) But the problem is that Sub-Table is very big (and is actually a view with some inner calculations) and this kind of subquery works way too long. So I suppose I need to filter out the table by m_id first and only after that find the field with the largest S_ORDER

我需要像这样的简单操作(之所以失败，是因为第二级子查询在外部看不到M.M_ID字段):

I need something simple like this (which fails because the second level subquery doesn't see the M.M_ID field outside):

SELECT m.*,
       (SELECT s_field
        FROM (SELECT s_field
              FROM t_sub s
              WHERE s.m_id = m.m_id
              ORDER BY s_order DESC)
        WHERE ROWNUM = 1) s_field
 FROM t_main m;

用于创建和填充测试模式的代码:

The code to create and populate the test schema:

CREATE TABLE t_main (m_id NUMBER PRIMARY KEY,
                     m_field VARCHAR2(10));
CREATE TABLE t_sub  (s_id NUMBER PRIMARY KEY,
                     s_field VARCHAR2(10),
                     s_order NUMBER,
                     m_id NUMBER );
INSERT INTO t_main VALUES (1,'main1');
INSERT INTO t_main VALUES (2,'main2');
INSERT INTO t_main VALUES (3,'main3');
INSERT INTO t_sub VALUES (1,'sub1-1', 1, 1);
INSERT INTO t_sub VALUES (2,'sub1-2', 2, 1);
INSERT INTO t_sub VALUES (3,'sub1-3', 3, 1);
INSERT INTO t_sub VALUES (4,'sub2-1', 1, 2);
INSERT INTO t_sub VALUES (5,'sub2-2', 2, 2);
INSERT INTO t_sub VALUES (6,'sub2-3', 3, 2);
INSERT INTO t_sub VALUES (7,'sub3-1', 1, 3);
INSERT INTO t_sub VALUES (8,'sub3-2', 2, 3);
INSERT INTO t_sub VALUES (9,'sub3-3', 3, 3);
COMMIT;

上述工作解决方案(对于大型T_SUB表，工作速度太慢):

Working solution mentioned above (working too slow with large T_SUB table):

SELECT m.*,
       s.s_field
FROM t_main m
LEFT JOIN
  (SELECT *
   FROM
     (SELECT ts.*,
             ROW_NUMBER() OVER (PARTITION BY m_id
                                ORDER BY s_order DESC) AS seq
      FROM t_sub ts)
   WHERE seq = 1) s ON s.m_id = m.m_id;

我们使用的数据库是Oracle 10g

The DB we use is Oracle 10g

非常感谢您的帮助

推荐答案

尝试一下

SELECT m.*,
       (select s.s_field 
          from t_sub s
         where s.m_id = m.m_id
           and s.s_order = (select max(s_order) from t_sub where t_sub.m_id = s.m_id)
           and rownum = 1)
FROM t_main m

或者您可以尝试一下(这是您的代码，但需要一些修改)

or you can try this (it's your code but some modifications)

SELECT m.*,
      (select s.s_field from 
       (SELECT s_field, m_id
          FROM t_sub
         --where t_sub.m_id = m.m_id
         order by s_order DESC) s
        where s.m_id = m.m_id
          and rownum = 1)
FROM t_main m

这篇关于Oracle:左连接非常大的表并将连接的行限制为最大字段值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！