MySQL学习笔记后续(开发技巧)

行转列

///此方法效率不高而且编写繁琐
///转换前
+------+-------+
| name | balls |
+------+-------+
| sjh  |    12 |
| sjh  |     5 |
| sjh  |     6 |
| hzy  |    25 |
| hzy  |    16 |
| hzy  |    11 |
| xgd  |     4 |
| xgd  |    36 |
| xgd  |    21 |
+------+-------+
select *from(
select sum(balls) as 'sjh' from ball where name='sjh' group by name) a cross join(
select sum(balls) as 'hzy' from ball where name='hzy' group by name) b cross join(
select sum(balls) as 'xgd' from ball where name='xgd' group by name) c;
///转换后
+------+------+------+
| sjh  | hzy  | xgd  |
+------+------+------+
|   23 |   52 |   61 |
+------+------+------+

行转列的高效方法

//case是当满足条件的时候执行后面语句
SELECT SUM(CASE WHEN name='sjh' THEN balls end) as 'sjh',
SUM(CASE WHEN name='hzy' THEN balls end) as 'hzy',
SUM(CASE WHEN name='xgd' THEN balls end) as 'xgd' FROM ball;