case when语句的用法
通过case when语句实现数剧的行转例,将数据按某字段分组,将一组中的数据显示在一行上
基本用法
SELECT
name,
(case sex when '男' then '01' when '女' then '02' when '人妖' then '03' else '04' end) sex, --写法一
(case when dept='市场部' then 'shichangbu' when dept='技术部' then 'jishubu' else '未知' end) --写法二
FROM
(VALUES
('张三','男','财务部',4000),
('赵红','未知','技术部',2000),
('李四','男','市场部',5000),
('李白','女','人事部',5000),
('王五','人妖','市场部',3000),
('王蓝','女','技术部',4000)
) AS EMPLOY(NAME,SEX,DEPT,SALARY)
行转列
create table score_001
(
banji integer,
kemu varchar(10),
fengshu integer
);
insert into score_001 values
(1, '物理', 100),
(2, '物理', 1420),
(1, '数学', 100),
(2, '英语', 20),
(3, '数学', 1233),
(3, '语文', 181),
(3, '物理', 1812);
SELECT * FROM score_001 ORDER BY BANJI asc
原表数据
select
banji,max(yuwen) 语文,max(shuxue) 数学,max(yingyu) 英语,max(wuli) 物理
from
(select
banji,
case kemu when '语文' then fengshu else 0 end yuwen,
case kemu when '数学' then fengshu else 0 end shuxue,
case kemu when '英语' then fengshu else 0 end yingyu,
case kemu when '物理' then fengshu else 0 end wuli
from score_001
) as inner
group by inner.banji
order by 1
转换之后