项目
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.这是一个将在一夜之间运行的报告,因此速度并不是那么重要,但是有很多项目(百万分之一);虽然针对每个项目有多个事件,但使用大范围意味着事件记录要少得多. 我想过的事情：
>以某种方式扩展项目/事件关系,以便针对所有单个项目记录事件.这将导致所考虑的数据量显着增加,但允许更简单的查询方法.
>以某种方式处理事件以约束或合并范围 – 如果我知道对于给定的项目,其所有事件具有相同的开始和结束,我可以简化事情.没想到通过.
解决方法
;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的最大事件日期.
