175.组合两个表
# Write your MySQL query statement below
SELECT Person.firstName,Person.lastName,Address.City,Address.State
FROM Person left join Address
ON Person.personid = Address.personid;
# 可以缩写表名:FROM Person p left join Address a
# on和where的区别:数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张
# 临时表返回给用户。 在使用left jion时,on和where条件的区别如下:
# 1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
# 2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
181.超过经理收入的员工
# Write your MySQL query statement below
SELECT name Employee
FROM Employee e
WHERE salary > (SELECT MIN(salary)
FROM Employee
WHERE e.managerId = id
);
# 虽然我自己想出来用了MIN函数,但是反而执行用时更长;
# 另外,要注意输出表头的更改和 WHERE e.managerId = id中e.表格的引入,除非是managerID=‘3’才不用表# 格引入。
182.查找重复的电子邮箱
# Write your MySQL query statement below
SELECT email
FROM person
GROUP BY email HAVING count(email)>1;
通过having进行条件筛查,having后面可以运用聚合函数
183.从不订购的客户
# Write your MySQL query statement below
SELECT name Customers
FROM Customers c LEFT JOIN Orders o
ON c.id = o.Customerid
WHERE o.id is NULL;
# 左连接,但是是找未选的,所以再用一个NULL
184.部门工资最高的员工
# Write your MySQL query statement below
SELECT d.name Department,e.name Employee,e.salary AS Salary
FROM Employee e LEFT JOIN Department d
ON e.departmentid = d.id
HAVING salary >= (
SELECT MAX(salary)
FROM Employee
WHERE departmentid = e.departmentid
)
# 不能WHERE后面跟MAX集函数等,只能再加一个条件
196.删除重复的电子邮箱
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE p1
FROM Person p1, Person p2
WHERE p1.email = p2.email AND p1.id > p2.id;
# 当我们需要删除表中的重复数据时,可以使用自连接和WHERE子句来实现。自连接是指将一个表与自身连接起来,这样我们就可以在同一张表中进行比较和筛选。
# 在这个例子中,我们需要删除Person表中重复的电子邮件,只保留一个id最小的唯一电子邮件。我们可以使用自连接将Person表与自身连接起来,然后使用WHERE子句过滤掉重复的电子邮件,并且保留id最小的电子邮件。具体来说,这个语句的含义是:
# 从Person表中选择两个不同的行,用p1和p2表示。
# 在这两个行中,如果它们的email相同且p1的id大于p2的id,则删除p1行。
197.上升的温度
# Write your MySQL query statement below
SELECT w.id
FROM Weather w JOIN Weather e
ON w.Temperature > e.Temperature
AND DATEDIFF(w.RECORDDATE,e.RECORDDATE) = 1;
# 注意不能使用左连接
# Write your MySQL query statement below
SELECT player_id,MIN(event_date) first_login
FROM Activity
GROUP BY player_id;
577.员工奖金
# Write your MySQL query statement below
SELECT e.name,b.bonus
FROM Employee e LEFT JOIN Bonus b
ON e.empid = b.empid
WHERE bonus <= '1000' OR bonus is NULL;
# 必须是LEFT,不能是JOIN
584.寻找用户推荐人
select name
from customer
where ifnull(referee_id,0)!=2;
# 简化写法
先把NULL转化成0;
586.订单最多的顾客
# Write your MySQL query statement below
SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(customer_number) DESC
LIMIT 1 #找到一条就不找了
#或者having count(order_number)>=all(
# select count(order_number)
# from orders
# group by customer_number
#)