PostgreSQL 9.3下不使用Pivot函数拆分语言至单独列

嘿，我明白你被这个问题困扰很久了！PostgreSQL 9.3确实不支持GROUPING SETS、ROLLUP这类高级分组功能，但咱们可以用窗口函数+条件聚合的组合来实现需求，完全适配9.3版本。

先纠正个小细节：你之前关联pupillanguage时写了pl.pupilid = p.id，但根据你给出的表结构，pupillanguage的外键是personid，所以得改成pl.personid = p.id，不然关联会出错哦。

接下来看具体解决方案：

步骤1：给每个学生的语言分配位次 #

先用窗口函数ROW_NUMBER()给每个学生掌握的语言按displayorder排序，每门语言都会得到一个专属位次（比如第一门语言是1，第二门是2）：

SELECT
  s.studentnumber AS studentnr,
  p.firstname AS name,
  sl.gradenumber AS gradenumber,
  l.text AS language,
  -- 按学生分组，根据displayorder排序分配位次
  ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY pl.displayorder) AS lang_rank
FROM student s
JOIN pupil p ON p.id = s.pupilid
JOIN pupillanguage pl ON pl.personid = p.id
JOIN language l ON l.id = pl.languageid
JOIN schoollevel sl ON sl.id = p.schoollevelid

步骤2：用条件聚合拆分语言到单独列 #

基于上面的子查询，我们用CASE语句配合MAX()聚合函数，把不同位次的语言提取到单独列中：

SELECT
  studentnr,
  name,
  gradenumber,
  MAX(CASE WHEN lang_rank = 1 THEN language END) AS language1,
  MAX(CASE WHEN lang_rank = 2 THEN language END) AS language2,
  MAX(CASE WHEN lang_rank = 3 THEN language END) AS language3,
  MAX(CASE WHEN lang_rank = 4 THEN language END) AS language4
  -- 可根据实际需求继续添加更多CASE语句，覆盖学生最多掌握的语言数量
FROM (
  SELECT
    s.studentnumber AS studentnr,
    p.firstname AS name,
    sl.gradenumber AS gradenumber,
    l.text AS language,
    ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY pl.displayorder) AS lang_rank
  FROM student s
  JOIN pupil p ON p.id = s.pupilid
  JOIN pupillanguage pl ON pl.personid = p.id
  JOIN language l ON l.id = pl.languageid
  JOIN schoollevel sl ON sl.id = p.schoollevelid
) AS ranked_languages
GROUP BY studentnr, name, gradenumber;

额外提示：确定需要多少列 #

如果你不确定学生最多掌握多少门语言，可以先跑这个查询统计：

SELECT MAX(lang_count) AS max_languages_per_student
FROM (
  SELECT COUNT(*) AS lang_count
  FROM pupillanguage
  GROUP BY personid
) AS language_counts;

得到结果后，对应添加足够的CASE语句即可。

这个方法的核心是用窗口函数给语言排序，再通过条件聚合把不同位次的语言“转置”到单独列里，完美适配PostgreSQL 9.3的特性，而且结果准确可控，不会像你之前尝试的LIMIT 1那样拿到随机语言。

内容的提问来源于stack exchange，提问作者Tito