如何使用SQLite按日期在层次结构中查找级别

Question

问题

如何使用带有递归表达式的SQLite，按日期为层次结构中的每个人找到级别。

详细信息

我发现下面的帖子详细介绍了在SQLite中使用递归查询对分层数据进行计算，以及在StackOverflow上使用SQLite3上的基本递归查询。

现在，在SQLite的文档中有一个很好的详细例子，说明如何从单个员工的角度处理分层数据的计算(第3.2部分的例子)

我做了什么

到目前为止，我知道如何计算所选个人的水平(woohoo)，但我无法弥合差距，并使此查询按日期应用于所有个人。

以下是我的部分查询，它为1个人完成了工作：

WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
SELECT org_1.id, org_1.boss_id, org_1.date_interest
FROM org org_1
WHERE id = 4 -- Here is the input for the individual
UNION
SELECT org_1.id, org_1.boss_id, org_1.date_interest
FROM org org_1
JOIN supervisor_of so
ON so.boss_id = org_1.id
AND so.date_interest = org_1.date_interest
)
SELECT *,
COUNT(id) AS level
FROM supervisor_of
GROUP BY date_interest
ORDER BY date_interest

输出：

| id   | boss_id | date_interest | level |
| ---- | ------- | ------------- | ----- |
| 4    | 2       | 2             | 3     |
| 4    | 2       | 3             | 3     |

但我无法想象会得到这样的结果：

| id   | boss_id | date_interest | level |
| ---- | ------- | ------------- | ----- |
| 1    |         | 1             | 1     |
| 2    | 1       | 1             | 2     |
| 3    | 1       | 1             | 2     |
| 1    |         | 2             | 1     |
| 2    | 1       | 2             | 2     |
| 3    | 1       | 2             | 2     |
| 4    | 2       | 2             | 3     |
| 1    |         | 3             | 1     |
| 2    | 1       | 3             | 2     |
| 3    | 1       | 3             | 2     |
| 4    | 2       | 3             | 3     |
| 5    | 4       | 3             | 4     |

以下是如何加载进行此测试的数据：

CREATE TABLE org(
id TEXT,
boss_id TEXT,
date_interest TEXT
);
-- 1st Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 1);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 1);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 1);
-- 2nd Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 2);
INSERT INTO org (id, boss_id, date_interest) VALUES(4, 2, 2);
-- 3rd Date
INSERT INTO org (id, boss_id, date_interest) VALUES(1, NULL, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(2, 1, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(3, 1, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(4, 2, 3);
INSERT INTO org (id, boss_id, date_interest) VALUES(5, 4, 3);

Answer 1

从代码中删除WHERE子句，以便查询所有id，并在递归CTE的第二部分中选择supervisor_of.id而不是org_1.id
最后，您必须按id分组，也可以：

WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
SELECT org_1.id, org_1.boss_id, org_1.date_interest
FROM org org_1
UNION
SELECT so.id, org_1.boss_id, org_1.date_interest
FROM org org_1 JOIN supervisor_of so
ON so.boss_id = org_1.id AND so.date_interest = org_1.date_interest
)
SELECT *, COUNT(*) AS level
FROM supervisor_of
GROUP BY date_interest, id
ORDER BY date_interest, id

请参阅演示
结果：

>级别<1>1<1><1>

id	boss_id	date_interest
1	空
2	1	1	2
3	1	1	2
1	空	2
2	1	2	2
3	1	2	2
4	2	2	3
1	空	3
2	1	3	2
3	1	3	2
4	2	3	3
5	4	3	4