这里我来演示下在POSTGREsql里面如何实现交叉表的展示，至于什么是交叉表，我就不多说了，度娘去哦。
原始表数据如下：
@H_404_4@t_girl=#select*fromscore; name|subject|score -------+---------+------- Lucy|English|100 Lucy|Physics|90 Lucy|Math|85 Lily|English|95 Lily|Physics|81 Lily|Math|84 David|English|100 David|Physics|86 David|Math|89 Simon|English|90 Simon|Physics|76 Simon|Math|79 (12rows) Time:2.066ms想要实现以下的结果：
@H_404_4@name|English|Physics|Math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89大致有以下几种方法：
1、用标准sql展现出来
@H_404_4@t_girl=#selectname,t_girl-#sum(casewhensubject='English'thenscoreelse0end)as"English",t_girl-#sum(casewhensubject='Physics'thenscoreelse0end)as"Physics",t_girl-#sum(casewhensubject='Math'thenscoreelse0end)as"Math" t_girl-#fromscore t_girl-#groupbynameorderbynamedesc; name|English|Physics|Math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:1.123ms2、用Postgresql 提供的第三方扩展 tablefunc 带来的函数实现
以下函数crosstab 里面的sql必须有三个字段，name,分类以及分类值来作为起始参数，必须以name,分类值作为输出参数。
@H_404_4@t_girl=#SELECT* FROMcrosstab('selectname,subject,scorefromscoreorderbynamedesc',$$values('English'::text),('Physics'::text),('Math'::text)$$) ASscore(nametext,Englishint,Physicsint,Mathint); name|english|physics|math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:2.059ms4、 存储函数实现
@H_404_4@createorreplacefunctionfunc_ytt_crosstab_py() returnssetofytt_crosstab as $ytt$ forrowinplpy.cursor("selectname,')astmpfromscoregroupbynameorderbynamedesc"): a=row['tmp'].split(',') yield(row['name'],a[0].split(':')[1],a[1].split(':')[1],a[2].split(':')[1]) $ytt$languageplpythonu; t_girl=#selectname,english,physics,mathfromfunc_ytt_crosstab_py(); name|english|physics|math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:2.687ms5、 用PLPGsql来实现
@H_404_4@t_girl=#createtypeytt_crosstabas(nametext,Englishtext,Physicstext,Mathtext); CREATETYPE Time:22.518ms createorreplacefunctionfunc_ytt_crosstab() returnssetofytt_crosstab as $ytt$ declarev_nametext:=''; v_englishtext:=''; v_physicstext:=''; v_mathtext:=''; v_tmp_resulttext:=''; declarecs1cursorforselectname,')fromscoregroupbynameorderbynamedesc; begin opencs1; loop fetchcs1intov_name,v_tmp_result; exitwhennotfound; v_english=split_part(split_part(v_tmp_result,2); v_physics=split_part(split_part(v_tmp_result,2); v_math=split_part(split_part(v_tmp_result,2); returnqueryselectv_name,v_english,v_physics,v_math; endloop; end; $ytt$languageplpgsql; t_girl=#selectname,English,Physics,Mathfromfunc_ytt_crosstab(); name|english|physics|math -------+---------+---------+------ Simon|90|76|79 Lucy|100|90|85 Lily|95|81|84 David|100|86|89 (4rows) Time:2.127ms 原文链接：https://www.f2er.com/postgresql/195492.html