SQL JOIN困难-似乎需要一种限制联接条件中的行的方法 [英] SQL JOIN difficulty - seem to need a way of limiting rows in a join condition

问题描述

我有点想找出如何设计Oracle查询的方法，尽管这里有类似的问题，但似乎都无法完全解决我所面临的问题.

I'm a bit stuck trying to figure out how to design an Oracle query, and although there are similar questions here none of them seem to quite address the issues I'm facing.

我有两个表，我想加入它们:

I have two tables and I want to join them:

PROJECT table
  PROJECT_ID   TITLE
  101          First project
  102          Second project
  103          Third project
  104          Fourth project
  105          Fifth project

EVENT table
  EVENT_ID  PROJECT_FK  EVENT_TYPE  EVENT_DATE  EVENT_DESC
  201       101         301         2010-01-01  First event
  202       101         301         2010-01-01  Second event
  203       101         302         2010-01-02  Third event
  204       102         301         2010-01-03  Fourth event
  205       102         301         2010-01-04  Fifth event
  206       104         301         2010-01-05  Sixth event
  207       105         302         2010-01-06  Seventh event

我想从PROJECT表中获得每个项目数据的列表，以及最新事件的详细信息，但仅是单一类型的事件(应忽略​​所有其他事件.)仅一行对于每个项目，应返回一行(因此，如果多个匹配事件具有相同的日期，则可以是一个，如果没有事件，则应在事件字段中返回null/blank.)

I would like to get a list of each project's data (from the PROJECT table) along with details of the most recent event, but only events of a single type (all other events should be ignored.) One row, and only one row, should be returned for each project (so if multiple matching events have the same date either one is fine, and if there are no events nulls/blanks should be returned in the event fields.)

这是输出的样子:

SELECT <???> WHERE PROJECT_ID IN (101, 102, 103, 105)   /* for event type 301 only */

  PROJECT_ID   TITLE           EVENT_DATE  EVENT_DESC
  101          First project   2010-01-01  First event
  102          Second project  2010-01-04  Fifth event
  103          Third project   NULL        NULL
  105          Fifth project   NULL        NULL

我发现这很棘手，因为我可以找到的唯一示例要么假设max(date)是唯一的(但是在此进行选择将返回错误的行)，或者它们假设存在很多重复，因此GROUP BY将起作用.

I'm finding this quite tricky as the only examples I can find either assume that the max(date) is unique (but here selecting by that will return the wrong row) or they assume there is a lot of duplication so GROUP BY will work.

推荐答案

Oracle 9i +，使用ROW_NUMBER:

SELECT x.project_id,
       x.title,
       x.event_date,
       x.event_desc
  FROM (SELECT p.project_id,
               p.title,
               e.event_date,
               e.event_desc,
               ROW_NUMBER() OVER(PARTITION BY p.project_id
                                     ORDER BY e.event_date) AS rank
          FROM PROJECT p
     LEFT JOIN EVENT e ON e.project_fk = p.project_id
                      AND e.event_type = 301
         WHERE p.project_id IN (101,102,103)) x
 WHERE x.rank = 1

Oracle 9i +，使用WITH和ROW_NUMBER:

WITH example AS (
     SELECT p.project_id,
            p.title,
            e.event_date,
            e.event_desc,
            ROW_NUMBER() OVER(PARTITION BY p.project_id
                                  ORDER BY e.event_date) AS rank
       FROM PROJECT p
  LEFT JOIN EVENT e ON e.project_fk = p.project_id
                   AND e.event_type = 301
      WHERE p.project_id IN (101,102,103))
SELECT x.project_id,
       x.title,
       x.event_date,
       x.event_desc
  FROM example x
 WHERE x.rank = 1

这篇关于SQL JOIN困难-似乎需要一种限制联接条件中的行的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！