sql - t-sql Tree Join to Values 以获得仅限于最新数据的无子节点的总和

Question

希望在 SQL (MS 2008 r2) 2 个相对简单的表中求和，虽然我正在努力寻找一种方法来使其工作，但我真的很感谢 SQL 专家的帮助:)

我将一个树型表连接到一个值集合，我们只检索值的最新实例(对于每个外键 x2)，每个值都有一个创建日期，我们只需要创建日期之前的最新值。最后我们只想对树底部的值求和(即没有 child 的值)。我阅读了有关 CTE 的信息，并认为这是让它发挥作用的好方法，但之前没有这样做过，所以仍然在努力阅读我写的内容:)

我有一个相对简单的树表，称为节点

[id] [int] IDENTITY(1,1) NOT NULL,
[parentID] [int] NOT NULL

我感兴趣的这张表的数据是这样的。 (现实中的简单示例可能有 1000-2000 条记录，最多可能有 7 层嵌套，通常只有 3 或 4 层)ID + ParentID 始终是唯一的。

ID      parentID
1077    1055
1110    1077
1103    1077
1104    1103
1105    1103
1111    1110
1112    1110

然后是我要加入的其他表 Allocations。

[Id] [int] IDENTITY(1,1) NOT NULL,
[creation] [datetime] NULL,
[crewId] [int] NULL,
[mealId] [int] NULL,
[allocation] [int] NULL

它是加入树的 crewId，它作为参数发送，因此我们将检索 1077 个 child 的总和。我只想包括无 child 值的总和。我想要 mealId 的每个值的总和。 crewId + mealId + creation 始终是唯一的。

Id          creation                crewId      mealId      allocation
----------- ----------------------- ----------- ----------- -----------
1           2012-04-13 16:50:00.000 1111        1085        1
2           2012-04-13 16:55:00.000 1111        1085        3
3           2012-04-13 17:03:31.100 1111        1085        2
4           2012-04-18 22:35:21.790 1112        1085        1
5           2012-04-18 22:35:32.630 1112        1086        1
6           2012-04-18 22:35:42.473 1112        1087        1
7           2012-04-25 18:15:53.117 1111        1086        1
8           2012-04-25 19:11:46.227 1111        1085        1
9           2012-04-25 19:11:46.227 1110        1085        5

这是我目前的情况，

declare  @crewId int
set @crewId = 1077

-- get total of allocated to childless children
;with 
Recurse as (
select 
  n.Id as DirectChildId
, n.Id
from umbracoNode n
where parentId = @crewId
union all
select
  b.DirectChildId
, n.Id
from umbracoNode n 
join Recurse b on b.Id = n.ParentId
)

select
  n.DirectChildId, mealId, sum(a.Allocation) as TotalAllocation
from Recurse n
left join Allocations a on n.Id = a.crewId
   and a.CrewId not in (select parentId from umbracoNode)
group by DirectChildId, mealId;

这几乎可行，但尚未考虑获取最新值。

我当前检索最新版本的查询如下所示。但我不知道如何将它们结合起来。

SELECT a.mealId, a.allocation
FROM Allocations AS a
  LEFT OUTER JOIN Allocations AS a2
  ON (a2.crewId = @crewId and a.MealId = a2.MealId AND a2.creation < a2.creation)
WHERE a.crewId = @crewId and a2.crewId IS NULL;

Answer 1

我让它工作了，这是我的答案:

ALTER PROCEDURE [dbo].[tegsGetTotalAllocated] 
(@crewId As Integer)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
;with q as (
SELECT n.id
FROM umbracoNode n
WHERE n.id = @crewId
UNION ALL
   SELECT n2.id
    FROM  umbracoNode n2
    JOIN q 
    ON n2.parentId = q.id
)

SELECT
  sum(a.allocation) as totalAllocated, a.mealId
FROM q
left join Allocation_Meal a on q.Id = a.crewId
   and a.CrewId not in (select parentId from umbracoNode)
where mealId is not null 
group by mealId
END

主要技巧是创建一个执行最新部分的 View 。我不太清楚这有什么影响。除了给我一种始终包含最新结果的虚拟表。这是否总是在每次命中时进行评估，还是比这更聪明？

CREATE VIEW [dbo].[Allocation_Meal]
AS
SELECT t1.mealId, t1.allocation, t1.crewId
FROM tegsAllocation AS t1
      LEFT OUTER JOIN tegsAllocation AS t2
    ON (t2.crewId = t1.crewId and t1.MealId = t2.MealId AND t1.creation < t2.creation)
WHERE t2.crewId IS NULL;

真的很感激任何关于它是否可以更有效率的想法或评论:)特别是我应该添加哪些索引来使这项工作更好，或者是识别这些索引的好方法:)