SQL case when 学习

最新推荐文章于 2024-04-14 18:27:34 发布

siashuayongsheng

最新推荐文章于 2024-04-14 18:27:34 发布

阅读量122

点赞数

文章标签： SQL

本文链接：https://blog.csdn.net/siashuayongsheng/article/details/83427858

版权

[color=darkred] 1：有member表(m_id int,m_sex varchar(20))其中m_sex有三个不同值('男','女','null'),
要求用一条SQL语句把member表的m_sex字段更新成m_sex的值('男')变成'女'，
'女'变成'男','null'不变；
答：update member
set m_sex =case m_sex when '女' then '男' when '男' then '女' else 'null'
end

2：有一张表table1，有三个字段分别是：年度，季度，数量；现在数据如下：
年度，季度，数量
1991 1 23
1991 2 32
1991 3 15
1991 4 20
1992 1 25
1992 2 65
要求用一条SQL语句查出结果为：
年份 1季度 2季度 3季度 4季度
1991 23 32 15 20
1992 25 65 0 0
答:select b.Year as '年份',sum(b.quarter1) as '1季度',sum(b.quarter2) as '2季度',
sum(b.quarter3) as '3季度',sum(b.quarter4) as '4季度' from(
select a.Year,
case a.quarter when 1 then a.Nums else 0 end as quarter1,
case a.quarter when 2 then a.Nums else 0 end as quarter2,
case a.quarter when 3 then a.Nums else 0 end as quarter3,
case a.quarter when 4 then a.Nums else 0 end as quarter4
from table1 as a)b
group by b.Year

SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two'ELSE 'other'END FROM test;

[/color]