第 2 部分：如何在不实际旋转的情况下获取基于分区的查询的总和

因此，由于我没有最新版本的 Postgresql（我有 Postgresql 9.3），所以我不可能想出一个类似于计算特定字段的总值的数据透视函数的函数。

您可以单击下面的链接来使用我创建的问题作为参考，它们在代码中类似，但在要求方面有所不同，但有第二个查询使用数组并且也产生完全相同的结果。

如何在不应用透视功能的情况下为不同字段创建列

我目前得到以下结果

我想要这些结果

下面我有一个疑问这会返回学生在 3 个不同列中所说的语言...我的问题是，我无法想出一个解决方案，可以返回有多少学生说 1 种语言 1、多少学生说语言 2 、多少学生说语言 3

with t as (
  SELECT s.studentnumber as studentnr, p.firstname AS name, 
sl.gradenumber as gradenumber, l.text as language,
  dense_rank() over (partition by s.studentnumber, 
p.firstname, sl.gradenumber order by l.text) as seqnum

  FROM student s JOIN 
       pupil p
       ON p.id = s.pupilid JOIN
       pupillanguage pl 
       ON pl.pupilid = p.id JOIN
       language l 
       ON l.id = pl.languageid JOIN
       schoollevel sl
       ON sl.id = p.schoollevelid
 )
select studentnr, name, gradenumber,
   max(case when seqnum = 1 then language end) as language_1,
   max(case when seqnum = 2 then language end) as language_2,
   max(case when seqnum = 3 then language end) as language_3
from t
group by studentnr, name, gradenumber;

我问这个问题是因为如果没有办法做到这一点，那么如果不可能的话我就没有必要进一步研究它。

分区和密集排序的整个概念对我来说相对较新，我不确定它们产生进一步结果的程度和能力。

使用您拥有的解决方案（任一解决方案，由于显而易见的原因，我更喜欢数组解决方案），将其放入 CTE 中，然后使用 UNION 计算总数：

with students as (
  select studentnr, 
         name, 
         gradenumber, 
         languages[1] as language_1,
         languages[2] as language_2,
         languages[3] as language_3,
         languages[4] as language_4,
         languages[5] as language_5
  FROM (       
    SELECT s.studentnumber as studentnr, 
           p.firstname AS name,
           sl.gradenumber as gradenumber,
           array_agg(DISTINCT l.text) as languages
    FROM student s
        JOIN pupil p ON p.id = s.pupilid    
        JOIN pupillanguage pl on pl.pupilid = p.id
        JOIN language l on l.id = pl.languageid
        JOIN schoollevel sl ON sl.id = p.schoollevelid
    GROUP BY s.studentnumber, p.firstname
  ) t
)
select *
from students
union all
select null as studentnr,
       null as name, 
       null as gradenumber, 
       count(language_1)::text,
       count(language_2)::text, 
       count(language_3)::text, 
       count(language_4)::text, 
       count(language_5)::text
from students;

聚合函数如count() ignore NULL值，因此它只会计算存在某种语言的行。

UNION 查询中所有列的数据类型必须匹配，因此如果第一个查询将该列定义为文本（或 varchar），则无法在第二个查询中返回该列中的整数值。这就是为什么结果是count()需要投射到text

第二个查询中的列别名并不是真正必要的，但我添加了它们以显示列列表必须如何匹配