我有一个示例表格名为 antest,如下所示,用于测试 crosstab 函数。
create table antest(student text, subject text, result numeric);
insert into antest(student, subject, result) values
('peter','music',2.0),
('peter','language',2.0),
('gabriel','history',8.0),
('john','history',9.0),
('john','maths',4.0),
('john','music',7.0);
student|subject|result
-------+-------+------
peter |music |2.0
peter |lanuage|2.0
gabriel|history|8.0
john |history|9.0
john |maths |4.0
john |music |7.0
期望的结果:
student|music|language|history|maths
-------+-----+--------+-------+-----
peter |2.0 |2.0 | |
gabriel| | |8.0 |
john |7.0 | |9.0 |4.0
我已经执行了以下查询:
我已经执行了以下查询:
select *
from public.crosstab (
'select student, subject, result from antest',
'select distinct subject from antest'
) as final_result(student text, music numeric, maths numeric, history numeric, language numeric);
我得到了以下结果:
student|music|maths|history|language
-------+-----+-----+-------+--------
peter |2.0 | | |2.0
gabriel| |8.0 | |
john |7.0 |9.0 |4.0 |
请告诉我我犯了什么错误。
我需要为另一个30GB大小并具有约75个属性的数据库重复此查询。是否有自动化的可能性?