代码之家 › 专栏 › 技术社区 › David Corsalini

使用单个查询计算组树中的人数

common-table-expression android-room android-sqlite sqlite

0

David Corsalini · 技术社区 · 5 年前

SQLite ,用于 Android ,使用这样的模式(这里非常简化,只是我需要的字段)

GROUP
group_id primary_key, 
parent_group_id nullable

PERSON
person_id primary_key,
parent_group

person 在一个 group 以及它的后代 组 s、鉴于 group_id 组 我想数一数。我想我需要一个CTE查询,我整个上午都在读关于它们的文章,但我不知道它们是如何工作的。

0 回复 | 直到 5 年前

1

Shawn 5 年前

你需要做CTE,这是对的。比如:

WITH tree AS
  (SELECT g.group_id AS root,
          g.group_id AS parent,
          p.person_id AS person
   FROM "group" AS g
   LEFT JOIN person AS p ON g.group_id = p.parent_group
   WHERE g.group_id = @desired_group
 UNION ALL
   SELECT t.root, g.group_id, p.person_id
   FROM tree AS t
   JOIN "group" AS g ON t.parent = g.parent_group_id
   LEFT JOIN person AS p on g.group_id = p.parent_group)
SELECT count(DISTINCT person)
FROM tree;

首先选择所需的组及其成员,然后递归地选择具有给定父组的组的所有成员。最后,统计找到的所有唯一用户。

db<>fiddle example

2

0

David Corsalini 5 年前

我浏览了所有我能找到的文章,通过大量的尝试和错误,我来到了这里(请注意,在我真实的数据库中) person 是 model

WITH RECURSIVE is_in_group(group_id, group_name, parent_group_id) AS(
    SELECT gr.group_id, gr.group_name, gr.parent_group_id FROM _group as gr WHERE gr.group_id = :groupId
    UNION ALL
    SELECT g.group_id, g.group_name, g.parent_group_id FROM _group as g
    JOIN is_in_group as c ON g.parent_group_id = c.group_id
    )
    SELECT q.group_id, q.group_name, count(m.model_id) as model_count FROM is_in_group as q
    LEFT JOIN _model m ON m.parent_group_id = group_id
    GROUP BY q.group_id

这会给我一份 group s(包括根),带有 group_id , group_name model_count 属于在每个 组 . 有了这个我就可以 sum 组\u id 想知道有多少人我们就在这里 组