一些sql话语练习

一些sql语句练习

SQL 按组汇总内容
1.原始数据

语文 90

数学 80

英语 100

要求实现

语文数学英语

90 80 100

实现方法：

select sum(case when kemu ='语文' then score end) as语文,

sum(case when kemu ='数学' then score end) as数学,

sum(case when kemu ='英语' then score end)英语

from t_testcore1

1.原始数据

语文 50

数学 60

英语 100

要求实现

语文数学英语

及格 不及格 优秀

（60一下不及格，60-80及格，80以上优秀）

实现方法：

select

(casewhen

( sum(casewhen kemu ='语文'then scoreend)) <60then'不及格'

when

(sum(casewhen kemu ='语文'then scoreend))between60and80 then'及格'

when

(sum(casewhen kemu ='语文'then scoreend)) >80then'优秀'end)

as语文,

(casewhen

( sum(casewhen kemu ='数学'then scoreend)) <60then'不及格'

when

( sum(casewhen kemu ='数学'then scoreend))between60and80then'及格'

when

( sum(casewhen kemu ='数学'then scoreend)) >80then'优秀' end )

as数学,

(casewhen(sum(casewhen kemu ='英语'then scoreend))<60then'不及格'

when(sum(casewhen kemu ='英语'then scoreend))between60and80then'及格'

when(sum(casewhen kemu ='英语'then scoreend)) >80then'优秀'end )

as英语

from t_testcore1

表结构：
student kemu fenshu
student1 语文 80
student1 数学 90
student1 英语 85
student2 语文 85
student2 数学 92
student2 英语 82

变成：
student 语文数学英语
student1 80 90 85
student2 85 92 82

select class,

sum(case when kemu ='语文' then score end) as 语文,

sum(case when kemu ='数学' then score end) as 数学,

sum(case when kemu ='英语' then score end) as 英语

from t_testscore

group by class

case when的作用就是一个条件选择语句,根据不同的要求显示不同的内容,格式是这样的case
when [选择条件]
then [结果1]
else [结果2]
end
其中[选择条件]也可以放在case之后。

举例1：表temp的字段是[rq]--日期,[shengfu]--胜负。
select rq,
sum(case when shengfu='胜' then 1 else 0 end)'胜',
sum(case when shengfu='负' then 1 else 0 end)'负'
from temp
group by rq;

举例2：表user的字段为id,username,password
select id,
(case username when 'admin' then 'root'
when 'Tite' then '帅哥'
when 'Ant' then '美女'
else '不帅' end) '管理员'
from user order by id;
注意：else后面如果没有添加处理的时候,那么没有判断的条件将输出NULL

，用一个SQL语句完成不同条件的分组。

Select country, sum(case when sex=’1’ then population else 0 end) as 男，-------男性人口

Sum(case when sex=’2’ then population else 0 end) as 女 ----------女性人口

From table group by country

已知数据按照另外一种方式进行分组，分析。

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。

SELECT SUM(population),

CASE country WHEN'中国'THEN'亚洲'

WHEN'印度'THEN'亚洲'

WHEN'日本'THEN'亚洲'

WHEN'美国'THEN'北美洲'

WHEN'加拿大' THEN'北美洲'

WHEN'墨西哥' THEN'北美洲'

ELSE'其他'END

FROM Table_A

GROUPBY

CASE country

WHEN'中国'THEN'亚洲'

WHEN'印度'THEN'亚洲'

WHEN'日本'THEN'亚洲'

WHEN'美国'THEN'北美洲'

WHEN'加拿大' THEN'北美洲'

WHEN'墨西哥' THEN'北美洲'

ELSE'其他'END;

六，在Case函数中使用合计函数

假设有下面一个表

学号(std_id)

课程ID(class_id)

课程名(class_name)

主修flag（main_class_flg)

100

经济学

100

历史学

200

历史学

200

考古学

200

计算机

300

计算机

400

化学

500

数学

有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。选修多门课程的学生，要选择一门课程作为主修，主修flag里面写入 Y。只选择一门课程的学生，主修flag为N(实际上要是写入Y的话，就没有下面的麻烦事了，为了举例子，还请多多包含)。
现在我们要按照下面两个条件对这个表进行查询

1.只选修一门课程的人，返回那门课程的ID

2.选修多门课程的人，返回所选的主课程ID

简单的想法就是，执行两条不同的SQL语句进行查询。
条件1
--条件1：只选择了一门课程的学生

SELECT std_id, MAX(class_id) AS main_class FROM Studentclass GROUPBY std_id HAVING COUNT(*) = 1;

执行结果1

STD_ID MAIN_class

300 4

400 5

500 6

条件2

--条件2：选择多门课程的学生 SELECT std_id, class_idAS main_class FROM Studentclass WHERE main_class_flg ='Y' ;

执行结果2

STD_ID MAIN_class

100 1

200 3

如果使用Case函数，我们只要一条SQL语句就可以解决问题，具体如下所示

SELECT std_id,

CASE

WHEN COUNT(*) = 1 --只选择一门课程的学生的情况 THEN MAX(class_id)

ELSE

MAX(CASEWHEN main_class_flg ='Y' THEN class_id ELSENULLEND )

ENDAS main_class

FROM Studentclass GROUPBY std_id;

运行结果

STD_ID MAIN_class

100 1

200 3

300 4

400 5

500 6

通过在Case函数中嵌套Case函数，在合计函数中使用Case函数等方法，我们可以轻松的解决这个问题。使用Case函数给我们带来了更大的自由度。
最后提醒一下使用Case函数的新手注意不要犯下面的错误
CASE col_1

WHEN 1 THEN'Right'

WHEN NULL THEN'Wrong'

END

在这个语句中When Null这一行总是返回unknown，所以永远不会出现Wrong的情况。因为这句实际表达的意思是

WHEN col_1 = NULL，这是一个错误的用法，这个时候我们应该选择用WHEN col_1 IS NULL。

七、小结

select与 case结合使用最大的好处有两点，一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。

下面举个简单的例子来说明。例如表 students(id, name ,birthday, sex, grade)，要求按每个年级统计男生和女生的数量各是多少，统计结果的表头为，年级，男生数量，女生数量。如果不用select case when，为了将男女数量并列显示，统计起来非常麻烦，先确定年级信息，再根据年级取男生数和女生数，而且很容易出错。

用select case when写法如下：
SELECT grade, COUNT (CASE WHEN sex = 1THEN 1
ELSE NULL
END)男生数,
COUNT (CASE WHEN sex = 2THEN 1
ELSE NULL
END)女生数
FROM students

GROUP BY grade;

-----------------------------------------

select s as '类别', count(s) as '人数' from

(

select score ,

case

when (score<60)then '不及格'

when (score>60)then '及格'

end as s

from t

group by a.s

练习：

select grade as 年级，count(case when sex=1 then 1 else null) 男生数

count(case when sex=2 then1 else null) 女生数

from students

group by grade

select s as类别，count(case when score<60 then 1 else null)不及格

count(case when score>60 then 1 else null)及格

from t

group by s

一些sql话语练习

热点推荐