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>-><wbr><wbr><wbr><wbr><wbr>IFNULL(RoomNo,'total') AS 房间,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='房租',FeeMoney,0)) AS 房租,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='水费',FeeMoney,0)) AS 水费,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='电费',FeeMoney,0)) AS 电费,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='卫生费',FeeMoney,0)) AS 卫生费,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='电视费',FeeMoney,0)) AS 电视费,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='网络费',FeeMoney,0)) AS 网络费,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>IFNULL(CDate, CDate) AS 记录时间,<br><wbr><wbr><wbr>-><wbr><wbr><wbr><wbr><wbr>SUM(IF(FeeName='total',FeeMoney,0)) AS total<br><wbr><wbr><wbr>-><wbr>FROM (<br><wbr><wbr><wbr>-> select no.RoomNo as RoomNo, IFNULL(f.FeeName, 'total') as FeeName, SUM(f.FeeMoney) as FeeMoney, f.CreateDate as CDate<br><wbr><wbr><wbr>-> from roomnoinfo no, Fee f<br><wbr><wbr><wbr>-> where no.bid=1 and no.beempty='full' and no.RoomNo=f.RoomNo and<br><wbr><wbr><wbr>-> f.CreateDate < '2011-03-31' and f.CreateDate >'2011-01-01'<br><wbr><wbr><wbr>-> GROUP BY RoomNO, FeeName<br><wbr><wbr><wbr>-> WITH ROLLUP<br><wbr><wbr><wbr>-> HAVING RoomNO IS NOT NULL<br><wbr><wbr><wbr>-><wbr>) AS A<br><wbr><wbr><wbr>-><wbr>GROUP BY RoomNo<br><wbr><wbr><wbr>-><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>
分享到:
相关推荐
mysql行转列(将同一列下的不同内容的几行数据,转换成几列显示)、列转行、行列汇总、合并显示
mysql数据值行转列的问题
MySQL转换PostgreSQL工具是一种软件或服务,用于将MySQL数据库迁移到PostgreSQL数据库。该工具可以帮助用户将MySQL的表结构、数据和查询语句转换为适用于PostgreSQL的格式,以便在两个不同的数据库系统之间进行平滑...
MySQL行转列与列转行,帮助需要的大佬学习行列之间的转换
即一列中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL...
(1)动态,适用于列不确定情况 create table table_name( id int primary key, col1 char(2), col2 char(2), col3 int ); insert into table_name values (1 ,'A1','B1',9), (2 ,'A2','B1',7), (3 ,'A3','B1'...
Mysql中使用存储过程,动态的把需要列转换为行。一个小小的例子,非常不错,有需要的可以下载。
8 MySQL 教程 8.1 联接和断开服务器 8.2 输入查询 8.3 常用查询的例子 8.3.1 列的最大值 8.3.2 拥有某个列的最大值的行 8.3.3 列的最大值:按组:只有值 8.3.4 拥有某个字段的组间...
7、NIFI综合应用场景-将mysql查询出的json数据转换成txt后存储至HDFS中 网址:https://blog.csdn.net/chenwewi520feng/article/details/130620392 本文是在6、NIFI综合应用场景-离线同步Mysql数据到HDFS中基础上完成...
8 MySQL 教程 8.1 联接和断开服务器 8.2 输入查询 8.3 常用查询的例子 8.3.1 列的最大值 8.3.2 拥有某个列的最大值的行 8.3.3 列的最大值:按组:只有值 8.3.4 拥有某个字段的...
* 0 译者序 * 1 MySQL的一般的信息 o 1.1 什么是MySQL? o 1.2 关于本手册 + 1.2.1 本手册中使用的约定 o 1.3 MySQL的历史 ... + 7.3.8 为列选择正确的类型 + 7.3.9 列索引 + 7.3.10 多列索引 +...
MySQL存储过程,一键批量修改一个表内的多个列(字段)类型。例如从int转换成varchar
NULL 博文链接:https://xkxjy.iteye.com/blog/602387
3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用AUTO_INCREMENT ...
将本地MySQL表转换为geojson结果 该脚本可与具有适当配置文件的任何MySQL数据库一起使用。 用法 该脚本将返回数据库中除坐标以外的所有数据。 对于坐标,如果尚未命名为“ lat”和“ lng”(例如,“纬度”和“经度...
3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用AUTO_INCREMENT ...
拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用...
3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用AUTO_INCREMENT ...
3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用AUTO_...
3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用AUTO_INCREMENT ...