sqlGirl

小陈小陈爱sql

2020年6月1日 /

以前觉得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
        )
;
作者  小陈小陈爱sql已关闭评论