时间戳

首页 > 解决方案 > 尝试基于 FK 关系循环对一系列行求和

sql - 尝试基于 FK 关系循环对一系列行求和

问题描述

我有一个包含故事信息的数据库表。
我希望能够将系列/所有系列中故事的所有字数相加，以获得“真实”字数

我的表格布局的相关列是 StoryId、Title、Words、Prequel 和 Sequel。

例如

12345, 'The Hobbit', 95356, NULL, 54321
54321, 'The Fellowship of the Ring', 187790, 12345, 32145
32145, 'The Two Towers', 156198, 54321, 54123
54123, 'The Return of the King', 137115, 32145, NULL
13579, 'Some other book', 1234, NULL, NULL

一个系列可以是任意数量的书籍，并且总是以 Sequel 列中的 NULL 结尾。

我正在尝试两个结果之一。

  1. （针对特定故事运行的临时查询）
StoryId, Title,      Words  Prequel Sequel Total
12345, 'The Hobbit', 95356, NULL, 54321, 474582

或（我怀疑这更容易，我可以稍后过滤）

  1. （针对整个表运行的查询）
StoryId, Title,      Words  Prequel Sequel Total
12345, 'The Hobbit', 95356, NULL, 54321, 474582
54321, 'The Fellowship of the Ring', 187790, 12345, 32145, 379226 (The sum of this and following stories)
32145, 'The Two Towers', 156198, 54321, 54123, 293313
54123, 'The Return of the King', 137115, 32145, NULL, 137115
13579, 'Some other book', 1234, NULL, NULL, 1234

我还没有找到用纯 sql 来做这件事的好方法，到目前为止，我只是在脑海中完成了数学运算，但是随着数据库的增长，这绝对是不可扩展的。

标签： sqlsql-server

解决方案

请稍后让我们知道您对此解决方案的实际用途:-)

这是一个非常有趣和愉快的挑战。

这是解决方案：

WITH storyMap AS(
    SELECT
        s.storyId, s.Title, s.Words,
        NULL AS Prequel, s.Sequel, s.Words AS Total,
        convert(varchar(max), NULL) AS SeqTitles
    FROM dbo.story s
    WHERE s.Sequel IS NULL
    ----------
    UNION ALL
    ----------
    SELECT
        s.storyId, s.Title, s.Words,
        s.Prequel, s.Sequel, s.Words + sm.Total as Total,
        isnull(sm.SeqTitles + ' / ', '') + sm.Title AS SeqTitles
    FROM
        dbo.story s
            JOIN storyMap sm
            ON sm.storyId = s.Sequel
)
SELECT *
FROM storyMap sm

您的样本的结果：

storyId 标题词 Prequel Sequel Total SeqTitles
54123 王者归来 137115 NULL NULL 137115 NULL
13579 其他书 1234 NULL NULL 1234 NULL
32145 两塔 156198 54321 54123 293313 王者归来
54321 魔戒187790 12345 32145 481103 王者归来/两塔
12345 霍比特人 95356 NULL 54321 576459 王者归来/两塔/魔戒

编辑

我的第一个给定解决方案从上一卷向后聚合：

WITH storyMap AS(
    SELECT
        s.storyId, s.Title, s.Words,
        NULL AS Prequel, s.Sequel, s.Words AS Total,
        convert(varchar(max), NULL) AS SeqTitles
    FROM dbo.story s
    WHERE s.Prequel IS NULL
    ----------
    UNION ALL
    ----------
    SELECT
        s.storyId, s.Title, s.Words,
        s.Prequel, s.Sequel, s.Words + sm.Total as Total,
        isnull(sm.SeqTitles + ' / ', '') + sm.Title AS SeqTitles
    FROM
        dbo.story s
            JOIN storyMap sm
            ON sm.storyId = s.Prequel
)
SELECT *
FROM storyMap sm

对于您的示例，此查询导致

storyId 标题词 Prequel Sequel Total SeqTitles
12345 霍比特人 95356 NULL 54321 95356 NULL
13579 其他书 1234 NULL NULL 1234 NULL
54321 魔戒 187790 12345 32145 283146 霍比特人
32145 两座塔楼 156198 54321 54123 439344 霍比特人/魔戒联谊会
54123 王者归来 137115 32145 NULL 576459 霍比特人/魔戒/两塔

推荐阅读