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 中截取指定字符前后的字符串