sqlGirl

小陈小陈爱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
        )
;
小陈小陈爱sql已关闭评论