左连接(即右表有没有都只返回左表)
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