sql - Oracle SQL [1..*] 关系 |在 [...*] 中排序后，从 [1...] 中选择所有行 + 从 [...*] 中选择顶行

Question

我想从表 TRAINEE 中选择 * + 最近 2 个月内的第一个 REPORT.DATE。

TRAINEE
+----+----------+
| ID | NAME     |   
+----+----------+   
| 1  | John Doe |
+----+----------+
| 2  | Jane Doe |
+----+----------+

REPORT
+------------+------------+---------------+
| TRAINEE_ID | DATE       | REPORT        |
+------------+------------+---------------+
| 1          | 01.07.2018 | Not Important |
+------------+------------+---------------+
| 1          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 1          | 03.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 03.07.2018 | Not Important |
+------------+------------+---------------+


Wanted results
+----+----------+--------------+
| ID | NAME     | FIRST_REPORT |
+----+----------+--------------+
| 1  | John Doe | 01.07.2018   |
+----+----------+--------------+
| 2  | Jane Doe | 02.07.2018   |
+----+----------+--------------+

我试过了...

这种方式只给了我一行有日期，剩下的是 NULL，因为连接只返回一行。删除 ROWNUM 过滤器将使我的查询为在 REPORT 中找到的每一行返回一行 TRAINEE。所以这也行不通。我必须在哪里放置 ROWNUM 过滤器？

SELECT 
    TRAINEE.*, 
    OUTER_ORDER_DATE.DATE 
FROM
    Trainee
LEFT JOIN 
    (
    SELECT 
        DATE,
        ID,
        ROWNUM as rnum
    FROM 
        (
            SELECT 
                 DATE,
                 ID, 
              FROM 
                 REPORT INNER_ORDER_DATE 
             WHERE 
                 INNER_ORDER_DATE.DATE >= add_months(sysdate,-2)
             ORDER BY 
                 INNER_ORDER_DATE.DATE ASC
                                 )  
    WHERE 
        rnum < 2
        ) ON OUTER_ORDER_DATE ON OUTER_ORDER_DATE.ID = a.ID

然后我尝试了以下查询，它有一些错误的语法；内部查询无法访问外部查询的 Trainee.ID。

SELECT 
    Trainee.*,
    (SELECT 
        DATE 
       FROM (
          SELECT 
               DATE 
            FROM 
               REPORT 
           WHERE
               ID = TRAINEE.ID 
             AND 
               DATE >= add_months(sysdate,-2)
           ORDER BY
               DATE ASC
                  ) 
      WHERE
          ROWNUM < 2
     ) as DATE
 FROM 
    TRAINEE

我的查询必须是什么样子才能获得上面想要的结果？

如果此问题已得到解答，请为我链接。我不知道如何搜索这种情况。谢谢你。

Answer 1

您应该尝试使用此代码:

SELECT trainee.id, trainee.name, report.report_date
  FROM trainee
       JOIN (  SELECT trainee_id, MIN (report_date) AS report_date
                 FROM report
                WHERE report_date >= ADD_MONTHS (SYSDATE, -2)
             GROUP BY trainee_id) report
           ON (report.trainee_id = trainee.id)