如何将SQL查询的结果转换为具有特定格式的html表 [英] How to transform results of an sql query to an html table with specific format
本文介绍了如何将SQL查询的结果转换为具有特定格式的html表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试为图书馆预订系统创建一些统计信息.我的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屋!
查看全文