MySQL数据库行转换为列的2种方法，今天在公司做个人工资结算查询的时候，需要从数据库取值(数据库为MySQL)，显示为类似工资条的效果，但是数据库的表结构需要进行行转换为列，数据库表结构如下：

处理前：

处理后：

首先，贴出我要转换的表的SQL语句，这是一个多表联查，行转换为列有点复杂，后面我会写一个单表的转换语句，以便理解。

SELECT u.id,u.true_name, m.meta_name, c.amount from

metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id

第一种方法：

SELECT      a.true_name,      MAX(          CASE a.meta_name          WHEN '底薪' THEN              a.amount                      ELSE              0          END      ) 底薪,      MAX(          CASE a.meta_name           WHEN '绩效奖金' THEN              a.amount                      ELSE              0         END      ) 绩效奖金,        MAX(          CASE a.meta_name          WHEN '工龄工资' THEN              a.amount                      ELSE                        0        END      ) 工龄工资,      MAX(          CASE a.meta_name           WHEN '全勤' THEN              a.amount                     ELSE                        0        END      ) 全勤  FROM      (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as aGROUP BY      a.true_name; 

因为查询的是工资，所以需要加一个else 0，当数据库值为null时，显示为0。

第二种方法：

SELECT DISTINCT  a.true_name,  (SELECT amount FROM (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as b WHERE a.true_name=b.true_name AND b.meta_name='底薪' ) AS '底薪',(SELECT amount FROM (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as b WHERE a.true_name=b.true_name AND b.meta_name='绩效奖金' ) AS '绩效奖金',(SELECT amount FROM (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as b WHERE a.true_name=b.true_name AND b.meta_name='工龄工资' ) AS '工龄工资',(SELECT amount FROM (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as b WHERE a.true_name=b.true_name AND b.meta_name='全勤' ) AS '全勤'      FROM (SELECT u.id,u.true_name, m.meta_name, c.amount from metadata m INNER JOIN charge c on c.charge_type=m.id INNER JOIN user u on c.user_id=u.id) as a

这个方法稍微复杂了一点，需要注意的是，这里用到了嵌套子查询，而且将一个子查询进行了不同的重命名，重复使用。

最后，为第二个方法写一个单表的示例，便于理解和使用

SELECT DISTINCT  a.sname,  (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',  (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'  FROM stdscore a