如何用SQL语句实现行列转换
行列转换是数据库系统中经常遇到的一个需求,在数据库设计时,为了适合数据的累积存储,往往采用直接记录的方式,而在展示数据时,则希望整理所有记录并且转置显示。图9.1展示了行列转换的功能。
图9.1 行列转换的需求 |
分析这个需求,可以发现希望做的是找出具有相同部门的记录,并根据其材料的值累加数量。如果手动来写的话,最终希望得到的是下面这样的SQL语句:
|
这是一个非常简单的查询语句,并且执行结果恰好就是希望得到的结果,但问题是,如何得知原表中究竟包含几种材料呢?显然,根据上述的SQL语句,得到的结果永远只能统计3种材料的消耗,这时候就需要动态地根据实际材料数目来得到查询语句。代码9-1实现了一个动态行列转换。
代码9-1 动态行列转换: Transfer.sql
|
为了书写方便,表名和列名都没有采用中文名字。建议读者在进行数据库设计时,尽量避免直接使用汉字,可以采用拼音或者缩写的方式来替代。
下面是这个SQL命令的执行结果:
Department | Item1 | Item2 | Item3 |
F1 | 3 | 1 | 2 |
F2 | 0 | 2 | 1 |
F3 | 1 | 0 | 1 |
这样的解决方案仍然有不少缺陷。主要有两点:第一是动态SQL命令执行效率往往不高,因为动态拼装的原因,导致数据库管理系统无法对这样的命令进行优化;第二是这样的SQL命令必须先确定其长度限制,而动态SQL命令的长度往往根据实际表的内容而改变,所以这个命令无法保证100%能够运行。
答案
行列转换的SQL命令通常需要依靠动态的SQL语句,具体的实现方法请参考本节的问题分析。