sql - 使用 SQL 从子组推断整体组

Question

我有许多子组，我试图用它们来推断整个组。可以把它想象成试图通过个人所学的类(class)来推断专业的要求，条件是:

不同专业的人不能上同一个类

没有人完成所有类(class)，但每个类(class)至少有一个人上过

所以从这里开始:

class    activity   individual
 1        fishin        A
 1        fishin        B
 2        trappin       A
 2        trappin       C
 3        hikin         B
 3        hikin         C
 4        jumpin        D
 4        jumpin        E
 5        rollin        E

会成为:

class    activity   individual    classes in major
 1        fishin        A          fishin, hikin, trappin
 1        fishin        B          fishin, hikin, trappin
 2        trappin       A          fishin, hikin, trappin
 2        trappin       C          fishin, hikin, trappin
 3        hikin         B          fishin, hikin, trappin
 3        hikin         C          fishin, hikin, trappin
 4        jumpin        D          jumpin, rollin
 4        jumpin        E          jumpin, rollin
 5        rollin        E          jumpin, rollin

我想这将需要 group_concat，但我试图找出整体查询。

Answer 1

您至少需要 sqlite 3.8.3 才能使用递归 CTE 来执行此操作:

with recursive x as (
  select
    individual,
    activity
  from
    tbl
  union
  select
    x.individual,
    t2.activity
  from
    x
        inner join
    tbl t1
        on x.activity = t1.activity
        inner join
    tbl t2
        on t1.individual = t2.individual
), y as (
  select
    individual,
    group_concat(distinct activity) g
  from
    x
  group by
    individual    
) select
  t.*,
  y.g
from
  y
    inner join
  tbl t
    on y.individual = t.individual;

我想不出一种方法来影响组中项目的顺序。此外，SQLFiddle 没有足够高的 SQLite 版本来展示演示。