MYSQL刷题

最新推荐文章于 2023-07-22 16:15:47 发布

ljdxgb

最新推荐文章于 2023-07-22 16:15:47 发布

阅读量122

点赞数

本文链接：https://blog.csdn.net/qq_33556348/article/details/114880249

版权

1179. 重新格式化部门表

select id,
       sum(case  when month = 'Jan' then revenue end) as Jan_Revenue,
       sum(case  when month = 'Feb' then revenue end) as Feb_Revenue,
       sum(case  when month = 'Mar' then revenue end) as Mar_Revenue,
       sum(case  when month = 'Apr' then revenue end) as Apr_Revenue,
       sum(case  when month = 'May' then revenue end) as May_Revenue,
       sum(case  when month = 'Jun' then revenue end) as Jun_Revenue,
       sum(case  when month = 'Jul' then revenue end) as Jul_Revenue,
       sum(case  when month = 'Aug' then revenue end) as Aug_Revenue,
       sum(case  when month = 'Sep' then revenue end) as Sep_Revenue,
       sum(case  when month = 'Oct' then revenue end) as Oct_Revenue,
       sum(case  when month = 'Nov' then revenue end) as Nov_Revenue,
       sum(case  when month = 'Dec' then revenue end) as Dec_Revenue
from department
group by id;
order by id;

CASE WHEN的用法：

SELECT
	NAME '英雄',
	CASE NAME
		WHEN '德莱文' THEN
			'斧子'
		WHEN '德玛西亚-盖伦' THEN
			'大宝剑'
		WHEN '暗夜猎手-VN' THEN
			'弩'
		ELSE
			'无'
	END '装备'
FROM
	user_info;

176. 第二高的薪水

临时表

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

IFNULL

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

DISTINCT用于去除重复值

mysql中isnull,ifnull,nullif的用法如下：

isnull(expr) 的用法： 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。 mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的。

isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。

IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

180. 连续出现的数字

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE l1.num=l2.num and l2.num=l3.num and l1.id=l2.id-1 and l2.id =l3.id-1;

本题不会的知识点，主要是form多个表，并且对多个表起了相应的别名（l1,l2,l3)，这种方式其实也是将三个表连接了起来，生成了一个表：

在这里插入图片描述

184. 部门工资最高的员工

SELECT 
*
FROM Department left JOIN Employee ON Department.id=Employee.DepartmentId

进行了连接
在这里插入图片描述

SELECT 
Employee.NAME,max(Employee.Salary)
FROM Department left JOIN Employee ON Department.id=Employee.DepartmentId 
GROUP BY Department.NAME;

出现错误
在这里插入图片描述
这里可以理解为，先生成了sam这条数据，然后再进行了统计，统计只能是统计然后输出一个数值，并不能实现这些复杂功能

SELECT DEPARTMENT.NAME AS 'Department',
EMPLOYEE.NAME AS 'Employee',
SALARY AS 'Salary'
FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENTID=DEPARTMENT.ID
WHERE (EMPLOYEE.DEPARTMENTID,SALARY) IN 
(SELECT DEPARTMENTID,MAX(SALARY) FROM EMPLOYEE GROUP BY DEPARTMENTID);

这题主要用了 WHERE的in匹配，先造出一个子表来，再对本表的内容进行筛选

178. 分数排名

select score, DENSE_RANK() OVER (ORDER BY Score DESC) as 'Rank'
from Scores;

626. 换座位

基础知识：from多表查询

SELECT * FROM SEAT S1,SEAT S2

多表查询逗号实际上相当于做了笛卡尔积，然后生成了一张新表，然后再这张新表中进行选取

{"headers": ["id", "student", "id", "student"], "values": [[5, "Jeames", 1, "Abbot"], [4, "Green", 1, "Abbot"], [3, "Emerson", 1, "Abbot"], [2, "Doris", 1, "Abbot"], [1, "Abbot", 1, "Abbot"], [5, "Jeames", 2, "Doris"], [4, "Green", 2, "Doris"], [3, "Emerson", 2, "Doris"], [2, "Doris", 2, "Doris"], [1, "Abbot", 2, "Doris"], [5, "Jeames", 3, "Emerson"], [4, "Green", 3, "Emerson"], [3, "Emerson", 3, "Emerson"], [2, "Doris", 3, "Emerson"], [1, "Abbot", 3, "Emerson"], [5, "Jeames", 4, "Green"], [4, "Green", 4, "Green"], [3, "Emerson", 4, "Green"], [2, "Doris", 4, "Green"], [1, "Abbot", 4, "Green"], [5, "Jeames", 5, "Jeames"], [4, "Green", 5, "Jeames"], [3, "Emerson", 5, "Jeames"], [2, "Doris", 5, "Jeames"], [1, "Abbot", 5, "Jeames"]]}

SELECT
(CASE
WHEN MOD(ID,2)=0  THEN ID-1
WHEN MOD(ID,2)=1 and ID!=C2.CC THEN ID+1
ELSE ID
END
)AS id,student
FROM SEAT,(SELECT COUNT(*) AS CC FROM SEAT) AS C2
ORDER BY id ASC;

先生成子表

(SELECT COUNT(*) AS CC FROM SEAT)

然后将其与seat连接，然后再进行筛选

1270. 向公司CEO汇报工作的所有人

SELECT DISTINCT e1.employee_id
FROM  Employees e1,Employees e2,Employees e3,Employees e4
WHERE  
e1.employee_id <> 1 and(
(e1.manager_id=e2.employee_id and e2.manager_id=1) or
(e1.manager_id=e2.employee_id and e2.manager_id=e3.employee_id and e3.employee_id=1) or
(e1.manager_id=e2.employee_id and e2.manager_id=e3.employee_id and e3.manager_id=e4.employee_id and e4.employee_id=1) );

相当于使用笛卡尔积生成了大全表，然后再使用条件查询

574. 当选者

select Name
from (
  select CandidateId as id
  from Vote
  group by CandidateId
  order by count(id) desc
  limit 1
) as Winner join Candidate
on Winner.id = Candidate.id;

先生成子表，再进行连接查找，思考连接与笛卡尔积的不同

1555. 银行账户概要

SELECT TRANS_ID,(PAID_BY-PAID_TO) AS PY, 
CASE(WHEN PY>0 THEN 'YES' ELSE 'NO' END) AS WJ
FROM TRANSACTIONS;

错误想法：再SELECT语句中，不可以使用上一个语句的结果，因为这两个语句是并列的
先查询出余额子表，然后再进行筛选

1045. 买下所有产品的客户

select customer_id
from Customer
group by customer_id
having count(distinct product_key) in 
(select count(distinct product_key) from Product)

group by的用法，having放在最后

185. 部门工资前三高的所有员工

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 salary)
      from Employee e2
     where 
      e1.salary<e2.salary and e1.DepartmentId=e2.DepartmentId);

SQL语句执行顺序：
1st) FROM字句:执行顺序为从后往前、从右到左。数据量较大的表尽量放在后面。

2nd) WHERE字句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE字句的最右。

3rd) GROUP BY:执行顺序从右往左分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉

4th) HAVING字句：消耗资源。尽量避免使用，HAVING会在检索出所有记录之后才对结果进行过滤，需要排序等操作。

5th) SELECT字句：少用号，尽量使用字段名称，oracle在解析的过程中，通过查询数据字典将号依次转换成所有列名，消耗时间。

6th) ORDER BY字句：执行顺序从左到右，消耗资源

在本题中，先运行from join语句，相当于得到了我们需要的大表。
再对大表进行where选择操作，对于其中的字段，都可以用于任何条件判断，此处使用了子表判断
最后再使用select语句对于其中的一些列进行选择

1212. 查询球队积分

自己想到的解法：使用三个表，首先对于赢了以及平局的情况进行分表，然后再集中进行查询，这种方法太过复杂，一看就是走上了邪路
分出的三个表：

(Select (CASE WHEN M.guest_goals>M.host_goals then M.guest_team WHEN M.host_goals>M.guest_goals then M.host_team END) AS WIN, 3 AS SCORE FROM Matches M WHERE M.host_goals !=M.guest_goals ) Group by WIN) AS win1,
(Select WIN,sum(SCORE) FROM (SELECT M.host_team AS WIN, 1 AS SCORE FROM Matches M WHERE M.host_goals=M.guest_goals ) Group by WIN ) AS win2,
(Select WIN,sum(SCORE) FROM (SELECT M.guest_team AS WIN, 1 AS SCORE FROM Matches M WHERE M.host_goals=M.guest_goals) Group by WIN) AS win3,

select t.team_id,t.team_name,
        ifnull(sum(case when m.host_goals>m.guest_goals and t.team_id=m.host_team then 3 
                        when m.host_goals=m.guest_goals and t.team_id=m.host_team then 1
                        when m.host_goals<m.guest_goals and t.team_id=m.host_team then 0
                        when m.host_goals>m.guest_goals and t.team_id=m.guest_team then 0
                        when m.host_goals=m.guest_goals and t.team_id=m.guest_team then 1
                        when m.host_goals<m.guest_goals and t.team_id=m.guest_team then 3
                   end),0) as num_points
from Teams t,Matches m
group by t.team_id
order by num_points desc,t.team_id asc

根据sql语句的相关优先级：

from Teams t,Matches m

语句相当于自动对两个表的笛卡尔积进行生成，生成了一张大表

{"headers": ["team_id", "team_name", "match_id", "host_team", "guest_team", "host_goals", "guest_goals"], "values": [[50, "Toronto FC", 1, 10, 20, 3, 0], [40, "Chicago FC", 1, 10, 20, 3, 0], [30, "Atlanta FC", 1, 10, 20, 3, 0], [20, "NewYork FC", 1, 10, 20, 3, 0], [10, "Leetcode FC", 1, 10, 20, 3, 0], [50, "Toronto FC", 2, 30, 10, 2, 2], [40, "Chicago FC", 2, 30, 10, 2, 2], [30, "Atlanta FC", 2, 30, 10, 2, 2], [20, "NewYork FC", 2, 30, 10, 2, 2], [10, "Leetcode FC", 2, 30, 10, 2, 2], [50, "Toronto FC", 3, 10, 50, 5, 1], [40, "Chicago FC", 3, 10, 50, 5, 1], [30, "Atlanta FC", 3, 10, 50, 5, 1], [20, "NewYork FC", 3, 10, 50, 5, 1], [10, "Leetcode FC", 3, 10, 50, 5, 1], [50, "Toronto FC", 4, 20, 30, 1, 0], [40, "Chicago FC", 4, 20, 30, 1, 0], [30, "Atlanta FC", 4, 20, 30, 1, 0], [20, "NewYork FC", 4, 20, 30, 1, 0], [10, "Leetcode FC", 4, 20, 30, 1, 0], [50, "Toronto FC", 5, 50, 30, 1, 0], [40, "Chicago FC", 5, 50, 30, 1, 0], [30, "Atlanta FC", 5, 50, 30, 1, 0], [20, "NewYork FC", 5, 50, 30...

接着进行：

group by t.team_id

相当于对于数据进行了分组，再通过前边的sum语句对于每一个分组内的数据进行汇总，显示

1112. 每位学生的最高成绩

Select  
   e.student_id,min(e.course_id) as course_id, e.grade
from
   Enrollments e,(Select  student_id ,max(grade) as mg from Enrollments group by student_id) m
where 
   e.student_id=m.student_id  and e.grade=m.mg 
group by e.student_id order by e.student_id;

先找出最大的成绩

Select  student_id ,max(grade) as mg from Enrollments group by student_id

相当于获得了笛卡尔大表，再通过where语句进行筛选，筛选出的那些列具有相同的成绩，因而可以汇总

如果只使用了min()而没有进行成绩筛选，则会

Select 
student_id, min(course_id),grade
from Enrollments

在这里插入图片描述

Select 
student_id, min(course_id),grade
from Enrollments order by student_id;

在这里插入图片描述
相当于min本身返回一个最小值，如果强行加入其他类，则程序会强行加入第一个

578. 查询回答率最高的问题

SELECT question_id as survey_log 
FROM
  (SELECT question_id,(count(answer_id)) AS ANS FROM survey_log WHERE answer_id is not null group by question_id)  AS T
ORDER BY T.ANS DESC LIMIT 1 OFFSET 0 ;

比较简单，先派生出排名表，然后在对排名表进行查询，注意：派生出的表必须有别名

1193. 每月交易 I

SELECT substring_index(trans_date,'-',2) as month , country,
count(amount) as trans_count,
sum(
case when state ='approved' then 1
else 0
end
)  as approved_count,
sum(amount) as trans_total_amount,
sum(
case when state='approved' then amount
else 0
end
)  as approved_total_amount
FROM Transactions  group by country,substring_index(trans_date,'-',2)

特别需要注意的是把

sum(
case when state ='approved' then 1
else 0
end
)  as approved_count,

改成：

count(
case when state ='approved' then 1
else 0
end
)  as approved_count,

后
在这里插入图片描述
产生了错误，因为count实际上是对不同的结果进行count，虽然不符合条件被置为0，但也是不同的结果，所以也会被count

知识点：
MySQL 中截取指定字符前后的字符串

ljdxgb

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL刷题

1179. 重新格式化部门表select id, sum(case when month = 'Jan' then revenue end) as Jan_Revenue, sum(case when month = 'Feb' then revenue end) as Feb_Revenue, sum(case when month = 'Mar' then revenue end) as Mar_Revenue, sum(case whe
复制链接

扫一扫