SQL基础

左连接（即右表有没有都只返回左表）

SELECT Person.FirstName, Person.LastName, Address.City, Address.State
FROM Person 
LEFT JOIN Address
ON Person.PersonId = Address.PersonId

返回空值，可以设置临时表，或者用isnull

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

拓展函数，返回第N高的薪资

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N=N-1;
    IF N < 0 THEN
    RETURN NULL;
    ELSE
  RETURN (
      # Write your MySQL query statement below.
      SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT N , 1),
    NULL) AS getNthHighestSalary
  );
  END IF;
END

分数排名，同分同名，且下一名不间隔，即排名出现[1,1，2……]的情况
PS： Rank 如果要保留为列名，需要加上 ` ``（非‘’）

SELECT Score , (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= a.Score) AS `Rank`
FROM Scores a
ORDER BY `Rank`

选择连续3相同的值

# Write your MySQL query statement below
SELECT DISTINCT a.num ConsecutiveNums
FROM Logs a
INNER JOIN Logs b ON a.id = b.id+1
INNER JOIN Logs c ON a.id = c.id+2
WHERE a.num = b.num AND a.num = c.num

查询超过经理收入的员工

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee a
INNER JOIN Employee b ON a.ManagerId = b.ID
WHERE a.Salary > b.Salary

查找重复值

# Write your MySQL query statement below
SELECT Email FROM Person 
GROUP BY Email
HAVING COUNT(Email)>1

用delete删除重复值
注意：在MYSQL中，不能先Select一个表的记录，再按此条件Update和Delete同一个表的记录，否则会出错：You can’t specify target table ‘xxx’ for update in FROM clause.

DELETE FROM Person 
WHERE Id NOT IN (
    SELECT * FROM(
        SELECT MIN(Id)
        FROM Person
        GROUP BY Email
    )a
);

左连接排除，即：查询左表有，右表没有的数据

# Write your MySQL query statement below
SELECT Name Customers FROM Customers LEFT JOIN Orders ON Customers.Id=Orders.CustomerId 
WHERE Orders.Id IS NULL

查找不同部门最大薪资

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

查找不同部门前三高薪资，题解思路（前三高的工资肯定是 它前面没有3个比它的工资还高了）

SELECT
    c.Name AS 'Department', a.Name AS 'Employee', a.Salary
FROM
    Employee a
        JOIN
    Department c ON a.DepartmentId = c.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT b.Salary)
        FROM
            Employee b
        WHERE
            b.Salary > a.Salary
                AND a.DepartmentId = b.DepartmentId
        )
;

查找今天温度>昨天温度的所有ID值，datadiff 函数返回两个日期差值

SELECT DISTINCT a.id
FROM Weather a
JOIN Weather b ON DATEDIFF(a.recordDate,b.recordDate)=1
WHERE a.Temperature > b.Temperature

大国家查询，面积超过300w 或者 人口超过2500w

SELECT name, population, area
FROM World
WHERE area > 3000000 OR population >25000000

查询相同课程，即相同值 ≥ 5的个数，但排除相同学生，即两个字段不能完全相同）

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student)>=5

连续相同的3个及以上值全大于100的所有记录，必须连续至少3次

SELECT DISTINCT a.*
FROM Stadium a, Stadium b, Stadium c
WHERE a.people >= 100 AND b.people >= 100 AND c.people >= 100
AND(
    (a.id=b.id+1 AND a.id=c.id+2) or
    (a.id=b.id-1 AND a.id=c.id+1) or
    (a.id=b.id-1 AND a.id=c.id-2)
)ORDER BY a.id

id为奇数且description ≠ ‘boring’ 的所有记录，并按照得分降序排列

SELECT * 
FROM cinema
WHERE description <> 'boring' AND mod(id,2)<>0
ORDER BY rating DESC