Mysql行转换为列

http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html

今晚需要统计数据生成简易报表，由原表格数据是单行的形式，最好转换为列表格式，由网上介绍方法实现如下：

希望获得的最终效果见下：

+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
| 房间<wbr>| 房租 | 水费<wbr>| 电费<wbr>| 卫生费 | 电视费 | 网络费 | 记录时间<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>| total<wbr><wbr>|<br> +-------+------+-------+-------+--------+--------+--------+---------------------+---------+<br> | 0201<wbr>|<wbr>400 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>10 |<wbr><wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr><wbr>0 | 2011-02-08 11:01:21 |<wbr><wbr><wbr><wbr>410 |<br> | 0204<wbr>|<wbr>150 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>10 |<wbr><wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr><wbr>0 | 2011-02-08 11:00:21 |<wbr><wbr><wbr><wbr>160 |<br> | 0206<wbr>|<wbr>150 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>10 |<wbr><wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr><wbr>0 | 2011-01-16 18:02:50 |<wbr><wbr><wbr><wbr>160 |<br> | 0302<wbr>|<wbr>350 | 40.92 | 18.91 |<wbr><wbr><wbr><wbr>20 |<wbr><wbr><wbr><wbr>50 |<wbr><wbr><wbr><wbr>50 | 2011-01-18 01:45:23 |<wbr>529.83 |<br> | 0306<wbr>|<wbr>150 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>10 |<wbr><wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr><wbr>0 | 2011-02-08 11:23:15 |<wbr><wbr><wbr><wbr>160 |<br> | 0308<wbr>|<wbr>200 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr>10 |<wbr><wbr><wbr><wbr><wbr>0 |<wbr><wbr><wbr><wbr><wbr>0 | 2011-03-28 22:26:41 |<wbr><wbr><wbr><wbr>210 |<br> | total | 1400 | 40.92 | 18.91 |<wbr><wbr><wbr><wbr>70 |<wbr><wbr><wbr><wbr>50 |<wbr><wbr><wbr><wbr>50 | 2011-03-28 22:26:41 | 1629.83 |<br> +-------+------+-------+-------+--------+--------+--------+---------------------+---------+</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

实现的SQL语句见下：

mysql><wbr>SELECT<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>IFNULL(RoomNo,'total') AS 房间,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='房租',FeeMoney,0)) AS 房租,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='水费',FeeMoney,0)) AS 水费,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='电费',FeeMoney,0)) AS 电费,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='卫生费',FeeMoney,0)) AS 卫生费,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='电视费',FeeMoney,0)) AS 电视费,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='网络费',FeeMoney,0)) AS 网络费,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>IFNULL(CDate, CDate) AS 记录时间,<br><wbr><wbr><wbr>-&gt;<wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='total',FeeMoney,0)) AS total<br><wbr><wbr><wbr>-&gt;<wbr>FROM (<br><wbr><wbr><wbr>-&gt; select no.RoomNo as RoomNo, IFNULL(f.FeeName, 'total') as FeeName, SUM(f.FeeMoney) as FeeMoney, f.CreateDate as CDate<br><wbr><wbr><wbr>-&gt; from roomnoinfo no, Fee f<br><wbr><wbr><wbr>-&gt; where no.bid=1 and no.beempty='full' and no.RoomNo=f.RoomNo and<br><wbr><wbr><wbr>-&gt; f.CreateDate &lt; '2011-03-31' and f.CreateDate &gt;'2011-01-01'<br><wbr><wbr><wbr>-&gt; GROUP BY RoomNO, FeeName<br><wbr><wbr><wbr>-&gt; WITH ROLLUP<br><wbr><wbr><wbr>-&gt; HAVING RoomNO IS NOT NULL<br><wbr><wbr><wbr>-&gt;<wbr>) AS A<br><wbr><wbr><wbr>-&gt;<wbr>GROUP BY RoomNo<br><wbr><wbr><wbr>-&gt;<wbr>WITH ROLLUP;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

源数据的表格式如下：

mysql> select RoomNo, CreateDate, FeeName, FeeMoney from Fee where bid=1;
+--------+---------------------+---------+----------+
| RoomNo | CreateDate<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>| FeeName | FeeMoney |<br> +--------+---------------------+---------+----------+<br> | 0101<wbr><wbr>| 2011-01-15 22:41:24 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>200 |<br> | 0101<wbr><wbr>| 2011-01-15 22:41:24 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>13.2 |<br> | 0101<wbr><wbr>| 2011-01-15 22:41:24 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>6.1 |<br> | 0102<wbr><wbr>| 2011-01-16 17:01:52 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>150 |<br> | 0102<wbr><wbr>| 2011-01-16 17:01:52 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr>145.2 |<br> | 0102<wbr><wbr>| 2011-01-16 17:01:52 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>67.1 |<br> | 0102<wbr><wbr>| 2011-01-16 17:01:52 | 卫生费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>10 |<br> | 0204<wbr><wbr>| 2011-02-08 11:00:21 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>150 |<br> | 0204<wbr><wbr>| 2011-02-08 11:00:21 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br> | 0204<wbr><wbr>| 2011-02-08 11:00:21 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br> | 0204<wbr><wbr>| 2011-02-08 11:00:21 | 卫生费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>10 |<br> | 0206<wbr><wbr>| 2011-01-16 18:02:50 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>150 |<br> | 0206<wbr><wbr>| 2011-01-16 18:02:50 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br> | 0206<wbr><wbr>| 2011-01-16 18:02:50 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br> | 0206<wbr><wbr>| 2011-01-16 18:02:50 | 卫生费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>10 |<br> | 0302<wbr><wbr>| 2011-01-18 01:42:35 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>150 |<br> | 0302<wbr><wbr>| 2011-01-18 01:42:35 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr>40.92 |<br> | 0302<wbr><wbr>| 2011-01-18 01:42:35 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr>18.91 |<br> | 0302<wbr><wbr>| 2011-01-18 01:42:35 | 卫生费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>10 |<br> | 0302<wbr><wbr>| 2011-01-18 01:45:23 | 卫生费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>10 |<br> | 0302<wbr><wbr>| 2011-01-18 01:45:23 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>200 |<br> | 0302<wbr><wbr>| 2011-01-18 01:45:23 | 网络费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>50 |<br> | 0302<wbr><wbr>| 2011-01-18 01:45:23 | 电视费<wbr>|<wbr><wbr><wbr><wbr><wbr><wbr>50 |<br> | 0306<wbr><wbr>| 2011-02-08 11:23:15 | 房租<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr><wbr>150 |<br> | 0306<wbr><wbr>| 2011-02-08 11:23:15 | 水费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br> | 0306<wbr><wbr>| 2011-02-08 11:23:15 | 电费<wbr><wbr><wbr>|<wbr><wbr><wbr><wbr>NULL |<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>