代码之家 › 专栏 › 技术社区 › Malvineous

SQL连接困难-似乎需要在连接条件中限制行的方法

greatest-n-per-group join oracle sql

Malvineous · 技术社区 · 15 年前

我一直在想如何设计一个Oracle查询,虽然这里也有类似的问题,但似乎没有一个能很好地解决我所面临的问题。

我有两张桌子,我想加入他们:

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

我想获得每个项目的数据列表(从项目表中)以及最近事件的详细信息,但仅返回单一类型的事件(应忽略所有其他事件)。每个项目应返回一行,且仅返回一行(因此,如果多个匹配事件具有相同的日期,则任何一个都可以,如果没有事件,则应在事件字段中返回空值

输出可能是这样的:

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)是唯一的(但这里选择by将返回错误的行),要么假设存在大量重复,因此groupby将起作用。

2 回复 | 直到 15 年前

OMG Ponies 15 年前

Oracle 9i+,使用行号:

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_编号:

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

anonymous 15 年前

每当您看到“仅适用于301事件类型”的内容时,您应该希望在where子句或having子句中看到相同的限制(“having”基本上是“group by”结果的where子句)。

这么说吧,你可以从下面的几句话开始,然后背单词。其中事件类型=301;

然后您可以填写一些基本信息,例如字段所在的表。选择* 从事件其中事件类型=301;

现在,您可以开始考虑根据PROJECT_ID将类似的事件分组在一起。为了使事情更简单,我们只使用PROJECT_FK,因为它相当于PROJECT.PROJECT_ID。选择项目从事件其中事件类型=301 按项目分组;

现在,事件表中的所有项目都分组在一起,但我们没有任何信息。我们正在寻找单个事件的详细信息,以便在有多个事件时只选择单个事件ID。MIN()和MAX()都可以。所以你可以写: 选择项目'u FK,MIN(事件'u ID) 从事件其中事件类型=301 按项目分组;

上面的查询为event表中的每个项目提供了一个事件,但没有提供其他事件详细信息。让我们把上面的结果集与事件表结合起来,我们就可以找到答案了。所以选择表2* 从事件表1 加入 ( 选择项目'u FK,MIN(事件'u ID) 从事件其中事件类型=301 按项目分组 )表2 表1.PROJECT_FK=表2.PROJECT_FK;

现在我们只需要加入项目表。每当您看到“事件字段中应该返回空值/空白值”时,您应该考虑外部连接。所以您可以继续链接表连接以获得所需的结果集。例如选择* 从项目左外部联接 ( 选择表2* 从事件表1 加入 ( 选择项目'u FK,MIN(事件'u ID) 从事件其中事件类型=301 按项目分组 )表2 表1.PROJECT_FK=表2.PROJECT_FK; ) 在PROJECT_ID=PROJECT_FK;