如何将SQL查询的结果转换为具有特定格式的html表 [英] How to transform results of an sql query to an html table with specific format

问题描述

我正在尝试为图书馆预订系统创建一些统计信息.我的sql查询结果看起来像下面的结构.

I am trying to create some statistics for a library reservation system. The result of my sql query looks like the following structure.

total_no_students| department  | property       | month  
 241             | Physics     | undergraduate  | Nov
 236             | Physics     | undergraduate  | Dec
 254             | Physics     | postgraduate   | Nov
 210             | Physics     | postgraduate   | Dec
 193             | Architecture| undergraduate  | Nov
 181             | Architecture| undergraduate  | Dec
 127             | Architecture| postgraduate   | Nov
 292             | Architecture| postgraduate   | Dec
 134             | Biology     | undergraduate  | Nov
 188             | Biology     | undergraduate  | Dec
 129             | Biology     | postgraduate   | Nov
 219             | Biology     | postgraduate   | Dec

我正在尝试使用php编写一些代码，以创建具有以下外观的统计信息表:

I am trying using php to write some code in order to create a statistics table with the following appearance:

    |Physics-undergrad|Physics-postgrad|Architecture-undergrad|Architecture-postgrad|
Nov |      241        |     254        |         193          |        127          |
Dec |      236        |     210        |         181          |        292          |

任何帮助如何将使用php的查询转换为上表的方法吗?非常感谢！

Any help how to trasform the query using php to the above table? Thanks a lot!

推荐答案

尝试一下:

SELECT A.month, 
       SUM(CASE WHEN A.department = 'Physics' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Physics_undergraduate, 
       SUM(CASE WHEN A.department = 'Physics' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Physics_postgraduate, 
       SUM(CASE WHEN A.department = 'Architecture' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Architecture_undergraduate, 
       SUM(CASE WHEN A.department = 'Architecture' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Architecture_postgraduate, 
       SUM(CASE WHEN A.department = 'Biology' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Biology_undergraduate, 
       SUM(CASE WHEN A.department = 'Biology' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Biology_postgraduate
FROM (...Your Existing Query...) AS A 
GROUP BY A.month

这篇关于如何将SQL查询的结果转换为具有特定格式的html表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！