select sum(case when kemu ='语文' then score end) as语文,
sum(case when kemu ='数学' then score end) as数学,
sum(case when kemu ='英语' then score end)英语
(sum(casewhen kemu ='语文'then scoreend)) <60then'不及格'
(sum(casewhen kemu ='语文'then scoreend))between60and80 then'及格'
(sum(casewhen kemu ='语文'then scoreend)) >80then'优秀'end)
( sum(casewhen kemu ='数学'then scoreend)) <60then'不及格'
( sum(casewhen kemu ='数学'then scoreend))between60and80then'及格'
( sum(casewhen kemu ='数学'then scoreend)) >80then'优秀' end )
(casewhen(sum(casewhen kemu ='英语'then scoreend))<60then'不及格'
when(sum(casewhen kemu ='英语'then scoreend))between60and80then'及格'
when(sum(casewhen kemu ='英语'then scoreend)) >80then'优秀'end )
sum(case when kemu ='语文' then score end) as 语文,
sum(case when kemu ='数学' then score end) as 数学,
sum(case when kemu ='英语' then score end) as 英语
Select country, sum(case when sex=’1’ then population else 0 end) as 男,-------男性人口
Sum(case when sex=’2’ then population else 0 end) as 女 ----------女性人口
有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为Primary Key)
根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。
简单的想法就是,执行两条不同的SQL语句进行查询。
条件1
--条件1:只选择了一门课程的学生
SELECT std_id, MAX(class_id) AS main_class FROM Studentclass GROUPBY std_id HAVING COUNT(*) = 1;
--条件2:选择多门课程的学生 SELECT std_id, class_idAS main_class FROM Studentclass WHERE main_class_flg ='Y' ;
如果使用Case函数,我们只要一条SQL语句就可以解决问题,具体如下所示
WHEN COUNT(*) = 1 --只选择一门课程的学生的情况 THEN MAX(class_id)
MAX(CASEWHEN main_class_flg ='Y' THEN class_id ELSENULLEND )
FROM Studentclass GROUPBY std_id;
在这个语句中When Null这一行总是返回unknown,所以永远不会出现Wrong的情况。因为这句实际表达的意思是
WHEN col_1 = NULL,这是一个错误的用法,这个时候我们应该选择用WHEN col_1 IS NULL。
select与 case结合使用最大的好处有两点,一是在显示查询结果时可以灵活的组织格式,二是有效避免了多次对同一个表或几个表的访问。
-----------------------------------------
select s as '类别', count(s) as '人数' from
select grade as 年级,count(case when sex=1 then 1 else null) 男生数
count(case when sex=2 then1 else null)女生数
select s as类别,count(case when score<60 then 1 else null)不及格