SQL连接到相关子查询,其中表通过重叠范围相关联

我有以下表结构：

项目

ID | Name
--------
1  | Apple
2  | Pear 
3  | Banana
4  | Plum
5  | Tomato

事件

ItemStart | ItemEnd | EventType | EventDate
--------------------------------------------
     1    |    2    |  Planted  | 2014-01-01
     1    |    3    |  Picked   | 2014-01-02
     3    |    5    |  Eaten    | 2014-01-05

这两个表仅由Item的主键和Event中的ItemStart和ItemEnd(包括)的范围链接.事件始终引用连续的项目序列,但并非所有给定项目的事件都具有相同的范围.事件永远不会在给定项目的同一日期发生.

我想要生成的查询如下：

列出所有项目,并为每个项目显示最近的事件

样本输出：

ID | Name   |   Event | Date
----------------------------
1  | Apple  |  Picked | 2014-01-02 (Planted then Picked)
2  | Pear   |  Picked | 2014-01-02 (Planted then Picked)
3  | Banana |  Eaten  | 2014-01-05 (Picked then Eaten)
4  | Plum   |  Eaten  | 2014-01-05 (Eaten)
5  | Tomato |  Eaten  | 2014-01-05 (Eaten)

这看起来很合理,如果有传统的外键关系(想象ItemID而不是ItemStart和ItemEnd),我可能会加入一个相关的子查询,如下所示：

SELECT Name,EventType,EventDate
FROM Item i
    INNER JOIN (
        SELECT ItemID,EventDate
        FROM Event e
        WHERE EventDate = (SELECT MAX(EventDate) FROM Event e_max WHERE e_max.ItemID = e.ItemID)
    ) latest_events ON i.ID = latest_events.ItemID

然而,由于范围关系到位我被卡住了,我想做更像这样的事情,但它不起作用：

SELECT Name,EventDate
FROM Item i
    INNER JOIN (
        SELECT ItemStart,ItemEnd,EventDate
        FROM Event e
        WHERE EventDate = (SELECT MAX(EventDate) FROM Event e_max WHERE i.ID >= e_max.ItemStart AND i.ID <= e_max.ItemEnd)
    ) latest_events ON i.ID >= latest_events.ItemStart AND i.ID <= latest_events.ItemEnd

我在第6行收到有关i.ID> = e_max.ItemStart AND i.ID< = e_max.ItemEnd的错误,因为您无法从联接的另一部分引用i.我想这样做(在更简单的例子中没有必要),因为当我构建子查询时,我不再有一个要链接的ID - 重叠范围意味着有许多可能的方法来包含单个项目,所以我想直接引用该项,其ID仅在顶级Item表中可用. 我希望这是有道理的. 我正在使用SQL Server 2008 R2.这是一个将在一夜之间运行的报告,因此速度并不是那么重要,但是有很多项目(百万分之一);虽然针对每个项目有多个事件,但使用大范围意味着事件记录要少得多. 我想过的事情：
>以某种方式扩展项目/事件关系,以便针对所有单个项目记录事件.这将导致所考虑的数据量显着增加,但允许更简单的查询方法.
>以某种方式处理事件以约束或合并范围 – 如果我知道对于给定的项目,其所有事件具有相同的开始和结束,我可以简化事情.没想到通过.

我该如何生成此查询？提前致谢！

解决方法

SQL Fiddle Demo

;with cte as 
(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY i.id ORDER BY e.EventDate DESC) as rNum
    FROM Item i
    JOIN Event e
        ON i.id between e.ItemStart and e.ItemEnd
)

SELECT ID,Name,EventDate FROM cte
WHERE rNum = 1

基本上,CTE已加入项目和事件,并为rownumber添加了一个新列,并在item.ID上进行了分区.这是它的外观截图.从这里我只选择rNum = 1,它应该是每个item.id的最大事件日期.

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!