Home » Code » Mysql——行转列

Mysql——行转列

有这么一个题目:表中有用户id-uid、月份month-值是1~12、收入sally三个字段,每个用户一个月可能有多份收入。一条sql语句列出每个用户一年12个月每个月的总收入,要求结果集中每个用户只有一条记录。初看到这个题目我愣了,一年有12个月,要显示每个月的总收入,怎么可能一个用户只有一条记录,不是应该12条么?其实它这个考的只是所谓的“行转列”,你不懂或者一时想不到只是说明接触得不够多,仅此而已。行转列后在一条记录中添加了12列,分别显示1~12月的收入。假如有数据如下:

row_column_1

可以使用IF语句:

SELECT uid,
SUM(IF(month=1,sally,0)) AS month_1,
SUM(IF(month=2,sally,0)) AS month_2,
SUM(IF(month=3,sally,0)) AS month_3 
FROM row_column 
GROUP BY uid 

结果如下:

row_column_2

IF语句的语法是:第一个参数是判断条件,如果为true,取第二个参数,否则取第三个参数。

把IF换成CASE,也是可以的:

SELECT uid,
SUM(CASE month WHEN 1 THEN sally ELSE 0 END) AS month_1,
SUM(CASE month WHEN 2 THEN sally ELSE 0 END) AS month_2,
SUM(CASE month WHEN 3 THEN sally ELSE 0 END) AS month_3 
FROM row_column 
GROUP BY uid 

使用子查询作为表达式也可以实现,如下如果某个用户某月份没有数据,结果为NULL:

SELECT a.uid,
(SELECT SUM(sally) FROM row_column b WHERE b.uid=a.uid AND b.month=1) AS month_1,
(SELECT SUM(sally) FROM row_column b WHERE b.uid=a.uid AND b.month=2) AS month_2,
(SELECT SUM(sally) FROM row_column b WHERE b.uid=a.uid AND b.month=3) AS month_3
FROM row_column a
GROUP BY uid 

以上都是硬编码,要完整的写完一年12个月就得有12种情况。如果要做到动态转换不写死,最好是写成存储过程,那就比较麻烦了,参考来源即可。

其实对于这种题目,网上流传更广的应该是查成绩这么一个例子。方法是基本一致的,可能需要把SUM求和改为MAX求最大值。

参考来源:http://www.williamsang.com/archives/1508.html

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.