- Department Highest Salary
Having 条件是用来筛选group的而不是用来筛选group内部条件的。
- Second Highest Salary
查询第二多花费得工资。Limit语句
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
如果不存在这种情况,直接会输出空字符,然而我们想让他输出null
如果遇到NULL的情况
再套一层,因为 select null会返回null
# Write your MySQL query statement below
select
(Select
Distinct Salary SecondHighestSalary
From
Employee
order by Salary Desc
limit 1,1) as SecondHighestSalary
同理,找到第N高的工资:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select
(Select
Distinct Salary SecondHighestSalary
From
Employee
order by Salary Desc
limit N,1) as SecondHighestSalary
);
END
- Swap Salary
update 题目
update Salary
set
sex = if (sex='m','f','m')
推荐使用case when end
UPDATE Salary
SET sex =
CASE
sex
WHEN 'm' THEN
'f' ELSE 'm' END;
- Rank Scores
rank 排序题目:
select Score,dense_rank() over(order by score desc) as "Rank" from scores
dense_rank()
rank()
over(partition by sth order by sth)
- Consecutive Numbers
连续数的思路
select
distinct l1.num 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
inner join的解法
# Write your MySQL query statement below
SELECT
DISTINCT M.num ConsecutiveNums
FROM
(
SELECT
l1.id,
l1.num
FROM
LOGS l1
INNER JOIN LOGS l2 ON l1.num = l2.num
AND l1.id = l2.id - 1
) M
INNER JOIN LOGS l3 ON M.num = l3.num
AND M.id = l3.id - 2
分辨好主体
select distinct(l.Num) ConsecutiveNums
from Logs l
inner join Logs l2 on l.Num = l2.Num and l.id = l2.id+1
inner join Logs l3 on l.Num = l3.Num and l.id = l3.id+2
- Exchange Seats
case when的例子:
select
(case
when id%2=1 and id =(select count(*) from seat) then id
when id%2=1 then id+1
else id-1
end
) as id,student
from
seat
ORDER BY id