mysql行列转换实例

2014 年 1 月 4 日 jslfl mssql

数据表：row_to_col,数据如下

scode | sval
--------------
up    |  2
down  |  3
left  |  4
right | 偏向右面

scode是主键,现希望得到的查询结果为：

up  |  down  |  left  |  right
-------------------------------
2   |   3    |   4    |  偏向右面

查询语句为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
MAX(t.vup) AS vup,
MAX(t.vdown) AS vdown,
MAX(t.vleft) AS vleft,
MAX(t.vright) AS vright
FROM(
SELECT
IF(scode = 'up',sval,'') AS vup,
IF(scode = 'down',sval,'') AS vdown,
IF(scode = 'left',sval,'') AS vleft,
IF(scode = 'right',sval,'') AS vright
FROM row_to_col
GROUP BY scode
) t

结果如下

vup  |  vdown  |  vleft  |  vright
-------------------------------------
2    |   3     |   4     |  偏向右面

因为sval值中有字符串，所以用max函数(用sum的话，字符串值会等于0)

其它使用参考http://www.jslfl.cn/?p=201

mysql

Comments are currently closed.

mysql行列转换实例

近期文章

近期评论

文章归档

分类目录

标签