LeetCode 数据库

配合《SQL进阶教程》做的题

 

 解法1：

CASE ... WHEN ..

ELSE END;

 

# Write your MySQL query statement below
UPDATE salary
SET 
   sex = CASE sex
        WHEN "m" THEN "f"
        ELSE "m"
    END;

解法2：

使用MySQL的if函数，类似于java的三目运算， if(sex = ‘m‘,‘f‘,‘m‘)，如果sex 为m，则返回f，如果为f，返回m。
update salary set sex = if(sex = ‘m‘,‘f‘,‘m‘)

 

 

# Write your MySQL query statement below
UPDATE salary set sex = if(sex = ‘m‘,‘f‘,‘m‘);

 

 

 

 解法1：

# Write your MySQL query statement below
select 
    `id`,
    max(if(`month` = ‘Jan‘, revenue, null)) as "Jan_Revenue",
    max(if(`month` = ‘Feb‘, revenue, null)) as "Feb_Revenue",
    max(if(`month` = ‘Mar‘, revenue, null)) as "Mar_Revenue",
    max(if(`month` = ‘Apr‘, revenue, null)) as "Apr_Revenue",
    max(if(`month` = ‘May‘, revenue, null)) as "May_Revenue",
    max(if(`month` = ‘Jun‘, revenue, null)) as "Jun_Revenue",
    max(if(`month` = ‘Jul‘, revenue, null)) as "Jul_Revenue",
    max(if(`month` = ‘Aug‘, revenue, null)) as "Aug_Revenue",
    max(if(`month` = ‘Sep‘, revenue, null)) as "Sep_Revenue",
    max(if(`month` = ‘Oct‘, revenue, null)) as "Oct_Revenue",
    max(if(`month` = ‘Nov‘, revenue, null)) as "Nov_Revenue",
    max(if(`month` = ‘Dec‘, revenue, null)) as "Dec_Revenue"
from
    Department
group by `id`;

 

 

 解法1：连接查询，然后组合两个条件

学到了DATEDIFF是两个日期的天数差集

select a.Id from Weather as a join Weather as b on a.Temperature> b.Temperature
and dateDiff(a.RecordDate,b.RecordDate) = 1

 

 

 

 

 

select max(Salary) AS SecondHighestSalary
from employee
where
salary < (select max(salary) from employee)

 

 

解法1：

 

 

 

# Write your MySQL query statement below
select s.Score,(select count(distinct Score)
                from Scores where Score >= s.Score)
                as Rank from Scores s
                 order by s.Score desc

 应该是最简单的SQL题：

 

 解法1：

 

 

 

# Write your MySQL query statement below
select name,population,area from world 
   where population > 25000000 or area > 3000000;

解法2：

 

 

 

 

select name,population,area from World where area > 3000000
 union 
     select name,population,area from World where population > 25000000;

 

 

 

 

解法1:

左连接

  

 

 

# Write your MySQL query statement below
select FirstName,LastName,City,State from Person left join Address
on Person.PersonId = Address.PersonId

 

 

 解法1：思路：

1. 判断表中筛重row大于N，否则输出null
2. 然后降序找出前N个，在升序获取第一个

 

 

 

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select if(
          (select count(*) from (select distinct Salary from Employee) a) < N,
          null,
          (select Salary as getNthHighestSalary from 
             (select distinct Salary from Employee order by Salary desc limit N) b
            order by Salary asc limit 1) 
      )
  );
END

 

 

新知识：

 

 要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

 

 

 注意：IS NULL 不是 = NULL

 

 

 解法1：

 

 其实这也产生了笛卡尔乘积

：

 

 

# Write your MySQL query statement below
SELECT
    a.Name AS ‘Employee‘
FROM
    Employee AS a,

  Employee AS b
Where 
    a.ManagerId = b.Id
    AND a.Salary > b.Salary 

解法2：
使用JOIN

 

 

# Write your MySQL query statement below
SELECT
   a.NAME as Employee
FROM Employee AS a JOIN Employee AS b
   ON a.ManagerId = b.Id
   AND a.Salary > b.Salary