今晚需要统计数据生成简易报表，由原表格数据是单行的形式，最好转换为列表格式，由网上介绍方法实现如下：
希望获得的最终效果见下：
+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
| 房间 | 房租 | 水费 | 电费 | 卫生费 | 电视费 | 网络费 | 记录时间 | total |
+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
| 0201 | 400 | 0 | 0 | 10 | 0 | 0 | 2011-02-08 11:01:21 | 410 |
| 0204 | 150 | 0 | 0 | 10 | 0 | 0 | 2011-02-08 11:00:21 | 160 |
| 0206 | 150 | 0 | 0 | 10 | 0 | 0 | 2011-01-16 18:02:50 | 160 |
| 0302 | 350 | 40.92 | 18.91 | 20 | 50 | 50 | 2011-01-18 01:45:23 | 529.83 |
| 0306 | 150 | 0 | 0 | 10 | 0 | 0 | 2011-02-08 11:23:15 | 160 |
| 0308 | 200 | 0 | 0 | 10 | 0 | 0 | 2011-03-28 22:26:41 | 210 |
| total | 1400 | 40.92 | 18.91 | 70 | 50 | 50 | 2011-03-28 22:26:41 | 1629.83 |
+-------+------+-------+-------+--------+--------+--------+---------------------+---------+
实现的SQL语句见下：
mysql> SELECT
-> IFNULL(RoomNo,'total') AS 房间,
-> SUM(IF(FeeName='房租',FeeMoney,0)) AS 房租,
-> SUM(IF(FeeName='水费',FeeMoney,0)) AS 水费,
-> SUM(IF(FeeName='电费',FeeMoney,0)) AS 电费,
-> SUM(IF(FeeName='卫生费',FeeMoney,0)) AS 卫生费,
-> SUM(IF(FeeName='电视费',FeeMoney,0)) AS 电视费,
-> SUM(IF(FeeName='网络费',FeeMoney,0)) AS 网络费,
-> IFNULL(CDate, CDate) AS 记录时间,
-> SUM(IF(FeeName='total',FeeMoney,0)) AS total
-> FROM (
-> select no.RoomNo as RoomNo, IFNULL(f.FeeName, 'total') as FeeName, SUM(f.FeeMoney) as FeeMoney, f.CreateDate as CDate
-> from roomnoinfo no, Fee f
-> where no.bid=1 and no.beempty='full' and no.RoomNo=f.RoomNo and
-> f.CreateDate < '2011-03-31' and f.CreateDate >'2011-01-01'
-> GROUP BY RoomNO, FeeName
-> WITH ROLLUP
-> HAVING RoomNO IS NOT NULL
-> ) AS A
-> GROUP BY RoomNo
-> WITH ROLLUP;
源数据的表格式如下：
mysql> select RoomNo, CreateDate, FeeName, FeeMoney from Fee where bid=1;
+--------+---------------------+---------+----------+
| RoomNo | CreateDate | FeeName | FeeMoney |
+--------+---------------------+---------+----------+
| 0101 | 2011-01-15 22:41:24 | 房租 | 200 |
| 0101 | 2011-01-15 22:41:24 | 水费 | 13.2 |
| 0101 | 2011-01-15 22:41:24 | 电费 | 6.1 |
| 0102 | 2011-01-16 17:01:52 | 房租 | 150 |
| 0102 | 2011-01-16 17:01:52 | 水费 | 145.2 |
| 0102 | 2011-01-16 17:01:52 | 电费 | 67.1 |
| 0102 | 2011-01-16 17:01:52 | 卫生费 | 10 |
| 0204 | 2011-02-08 11:00:21 | 房租 | 150 |
| 0204 | 2011-02-08 11:00:21 | 水费 | NULL |
| 0204 | 2011-02-08 11:00:21 | 电费 | NULL |
| 0204 | 2011-02-08 11:00:21 | 卫生费 | 10 |
| 0206 | 2011-01-16 18:02:50 | 房租 | 150 |
| 0206 | 2011-01-16 18:02:50 | 水费 | NULL |
| 0206 | 2011-01-16 18:02:50 | 电费 | NULL |
| 0206 | 2011-01-16 18:02:50 | 卫生费 | 10 |
| 0302 | 2011-01-18 01:42:35 | 房租 | 150 |
| 0302 | 2011-01-18 01:42:35 | 水费 | 40.92 |
| 0302 | 2011-01-18 01:42:35 | 电费 | 18.91 |
| 0302 | 2011-01-18 01:42:35 | 卫生费 | 10 |
| 0302 | 2011-01-18 01:45:23 | 卫生费 | 10 |
| 0302 | 2011-01-18 01:45:23 | 房租 | 200 |
| 0302 | 2011-01-18 01:45:23 | 网络费 | 50 |
| 0302 | 2011-01-18 01:45:23 | 电视费 | 50 |
| 0306 | 2011-02-08 11:23:15 | 房租 | 150 |
| 0306 | 2011-02-08 11:23:15 | 水费 | NULL |
| 0306 | 2011-02-08 11:23:15 | 电费 |