图1 原先表内容及布局
如何到下图2呢?
图2 处理后的输出(表没动)
已知表结构如图3:
图3 student表结构
环境: MySql
这里介绍两种方法:
(1) 常规方法: 同一个表命名多次,并列出现 , 如 student a, student b...
mysql> select a.username UserName,
a.score 'math',
b.score 'literature',
c.score 'computer'
from student a, student b, student c
where a.username = b.username and
b.username=c.username and
a.subject='math' and
b.subject='literature' and
c.subject='computer';
(2)采用CASE WHEN:
CASE WHEN用法
CASE WHEN用法
--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end
--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
mysql> select username,
sum(case when subject='math' then score else 0 end) as 'math',
sum(case when subject='literature' then score else 0 end) as 'literature',
sum(case when subject='computer' then score else 0 end) as 'computer'
from student group by username;
------ 或----
select username, sum(case subject when 'math' then score else 0 end) 'math',
sum(case subject when 'literature' then score else 0 end) 'literature',
sum(case subject when 'computer' then score else 0 end) 'computer'
from student group by username;
说明:
1. sum(case when subject='math' then score else 0 end) as 'math' 中的‘as’可以省略,且命名可以是中文,引号也可不加。
2. sum(...)也可换成max(...)。
3. 尾部 group by username必须得加上,否则报错:ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'longdatabase.student.username';
常见错误:
select username, (case when subject='math' then score end) as 'math',
(case when subject='literature' then score end) as 'literature',
(case when subject='computer' then score end) as 'computer'
from student;
总结:方法一效率低,需要重复多次查找一个表,尤其不适合大表(Big Table)。