小陈小陈爱sql
以前觉得sql很简单,刷了牛客的几十道sql,就以为自己会了,可是现实给了我重重一击,原来好多语法我都不会,sql语言并不简单。因为以前的自己不考虑未来,年后才开始打算未来的方向,错过了很多提升自己的机会。眼看秋招临近,心中也是百感交集,不过,着急是没什么用的,虽然平衡学校课程和工作实力都将我压得喘不过气,这两周茶不思饭不想,只想快点学完,可是时间哪里等我呢。我只有更加努力一些才好,那么请看以下几道题吧。
一.case… when…then…练习题1
原表格如下:
select * from team;
+——+——–+
| id | result |
+——+——–+
| 1 | 胜 |
| 1 | 负 |
| 2 | 负 |
| 3 | 负 |
+——+——–+
写出sql语句得出以下表格(统计每个人的胜负次数):
+——+——+——+
| id | 胜 | 负 |
+——+——+——+
| 1 | 1 | 1 |
| 2 | 0 | 1 |
| 3 | 0 | 1 |
+——+——+——+
涉及知识点:group by + case when
答案如下:
select id,
sum(case when result=’胜’ then 1 else 0 end) as ‘胜’,
sum(case when result=’负’ then 1 else 0 end) as ‘负’
from team
group by id;
看完答案,你可能会想,为什么用的是sum而不是count?那么用count计算得到的结果是什么呢?计算结果如下:
select id,
count(case when result=’胜’ then 1 else 0 end) as ‘胜’,
count(case when result=’负’ then 1 else 0 end) as ‘负’
from team
group by id;
+——+—-+—-+
| id | 胜 | 负 |
+——+—-+—-+
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
+——+—-+—-+
结果显然不对,那么为什么不对呢?在原表中,id=1的数据项有两行,id=2和id=3的数据项有一行,而返回结果不管每个人胜负多少,返回的结果都是此id在原表中所占有的行数。两者区别如下:
count:
- COUNT()函数里面的参数是列名的的时候,那么会计算有值项的次数。(NULL 不计入, 但是”值计入);
- COUNT(*)可以计算出行数,包括null ,COUNT(1)也可以计算出行数,1在这里代表一;
- COUNT(条件表达式),不管记录是否满足条件表达式,只要非NULL就加1 ,所以一般都count(id=1 or null);
sum:
- sum()参数是列名的时候,计算列名的值的相加,不是统计有值项的总数;
- sum(id=2) 当参数是表达式的时候,统计满足条件的行;
二.case… when…then…练习题2
原表格如下:
select * from team1;
+——+——+——+
| id | x | y |
+——+——+——+
| 1 | 胜 | 负 |
| 1 | 负 | 胜 |
| 2 | 胜 | 胜 |
| 3 | 负 | 胜 |
+——+——+——+
其中x代表第一场比赛,y代表第二场比赛。
编写sql语句返回以下结果:
+——+——+——+
| id | win | loss |
+——+——+——+
| 1 | 2 | 2 |
| 2 | 2 | 0 |
| 3 | 1 | 1 |
+——+——+——+
答案如下:
select id,
sum(case when x=’胜’ then 1 else 0 end) + sum(case when y=’胜’ then 1 else 0 end) as win,
sum(case when x=’负’ then 1 else 0 end) + sum(case when y=’负’ then 1 else 0 end) as loss
from team1
group by id;
三.group by 练习题3
力扣185题:https://leetcode-cn.com/problems/department-top-three-salaries/
法一:窗口函数
select d.Name Department,t1.Name Employee,Salary from Department d, ( select DepartmentId,Id,Name,Salary,dense_rank() over(partition by DepartmentId order by salary desc) 'rank' from Employee ) t1 where t1.rank<=3 and t1.DepartmentId=d.Id;
法二:
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id WHERE 3 > (SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2 WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId ) ;