sql刷题（leetcode非会员版）

limit start, count。其中start的显示值是从start+1开始的。但此处输入不能是计算式，比如：N-1
第N高的N，是通过自定义函数getNthHighestSalary的(N INT)中N传入。start必须是从N-1开始，才能显示符合题目要求的结果。比如第N=2高，如果直接用N值到limit，limit 2,1，意为从第3行开始，显示一行。所以要用N-1=1，才能表示从第二行开始。
这时，应通过一个替代参数实现。MySQL自定函数中的参数是静态参数，即要先定义后使用。先用declare定义类型，后通过set进行赋值。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m INT;
    set m=N-1; 
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((select distinct Salary from Employee as E order by Salary desc limit m,1),null)
  );
END

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
利用用户变量实现对连续出现的值进行计数：

查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。
select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
与自关联或自连接相比，这种方法的效率更高，不受Logs表中的Id是否连续的限制，而且可以任意设定某个值连续出现的次数。

针对评论区朋友的疑问，这里做下解答：

1）逻辑：构建两个变量@prev 和@count ，前者用于与Num做比较判断，后者用于@prev和Num相等时的条件计数；

2）(select @prev := null,@count := null) as t 这句的作用是初始化两个变量，并将初始化后的变量放到一张临时表t中，:=符号在MySQL中是赋值的意思；

3）when @prev = Num then @count := @count + 1和when (@prev := Num) is not null then @count := 1 这两个语句不能交换顺序，赋值语句永远非NULL，所以一旦执行顺序来到了第二个when，@count 是一定会被赋值为1的，后者放到前面的话就达不到计数的目的；

4）(@prev := Num) is not null这部分去掉后面加的判断，SQL也能正常执行，上面SQL中case when的这种用法，when后是判断条件，赋值后又加判断，我原以为这样会好理解点；

5）case when本质是一个函数，有值时就返回内部处理得到的值，无值就返回NULL，针对每一个Num，上面SQL中的case when 都会有一个计数，并把这个计数返回给CNT。

PS：MySQL8.0以后的版本开始支持窗口函数，使用窗口函数也能很好的解决此类问题。关于MySQL的窗口函数，可以参考译文：https://blog.csdn.net/qq_41080850/article/details/86416106

查找 Person 表中所有重复的电子邮箱。
-- 解法1
select email from person group by email having count(email)>1

--解法2
select email from (select count(1) as t,email from person group by email)r  where r.t>1;

--解法3
select distinct(p1.Email) from Person p1  
join Person  p2 on p1.Email = p2.Email AND p1.Id!=p2.Id
--
select Email
from Person
group by Email
having count(Email) > 1;

# Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户
# select name from customers left join orders having customers.id not in orders.customerId
select Name Customers From
Customers left join Orders on Customers.id = Orders.CustomerId where Orders.id is null

# 498ms
select c.Name as Customers from Customers c left join Orders o on o.CustomerId = c.Id where o.Id is null;

# 532ms
select c.Name as Customers from Customers c where not exists (select 1 from Orders o where o.CustomerId = c.Id);

# 455ms
select c.Name as Customers from Customers c where c.Id not in (select distinct o.CustomerId from Orders o);

# 编写SQL查询以查找每个部门中薪资最高的员工
# 最高工资两个人时报错
# select D.name Department,E.name Employee,MAX(salary) Salary from Employee e left join Department D on e.departmentId=D.id group by departmentId 
# 注意：有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息。
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
	)
;

# 一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

# 编写一个SQL查询，找出每个部门中 收入高的员工 。
# 找
# select distinct salary,departmentId from Employee group by departmentId order by salary desc 
 
SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;
# 编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。
delete p1 from Person p1 ,Person p2
where p1.Email =p2.Email and p1.Id > p2.Id 

 #  编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
# select w2.id from weather w1 left join weather w2 on w1.id=w2.id where w1.recordDate<w2.recordDate and w2.temperature >w1.temperature
SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature
;

 
 

# 写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。
# 遇见null会报错 select name from customer where referee_id!=2
select name from customer where ifnull(referee_id,0)!=2
# 下了 最多订单 的客户 恰好有一个客户 比任何其他客户下了更多的订单。
# .max和count不能直接连用，要是想取count后的最大值，可以用降序排序后取第一个的做法代替，order和count可以一起用
# select Max(Count(order_number))from Orders group by customer_number
SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
;

# 如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

# 面积至少为 300 万平方公里（即，3000000 km2），或者
# 人口至少为 2500 万（即 25000000）
# 编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
# 法一：select name,population ,area from  World where area>=3000000 or population >=25000000
SELECT
    name, population, area
FROM
    world
WHERE
    area >= 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population >= 25000000
;

 

# 写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序
# 树中每个节点属于以下三种类型之一：
# 叶子：如果这个节点没有任何孩子节点。
# 根：如果这个节点是整棵树的根，即没有父节点。
# 内部节点：如果这个节点既不是叶子节点也不是根节点
# select t1.id from tree t1 join tree t2 on t1.id not in t2.p_id
# select t1.id from tree t1 where t1.p_id=null
# select t1.id from tree t1 join tree t2 on t1.id in t2.p_id and t1.p_id !=null
# 法一；
# select id,
# if(p_id is null,"Root",
#     if(id in (select p_id from tree),"Inner","Leaf")) as type
# from tree
select id,
case when t.p_id is null then 'Root' 
     when t.id in (select p_id from tree ) then 'Inner'
     else 'Leaf' 
     end as Type
from tree t 
  在sql中, null是缺失未知值而不是空值 
当询问 id not in (select p_id from tree)时, 因为p_id有null值, 返回结果全为false, 于是跳到else的结果, 返回值为inner. 所以在答案中,leaf结果从未彰显,全被inner取代.
下述语句得出的结果从来不会返回Leaf值.

select
    id,
    case when p_id is null then "Root"
         when id not in (select p_id from tree) then "Leaf"
         else "Inner"
    end as Type
from
    tree

# 编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
# 按 id 升序 返回结果表。
# 利用异或只把偶数减2，奇数不变，从而调位。（单数-1后，最后一位是0，异或把最后一位变回1，等于不变；偶数-1后，最后一位是1，异或把最后一位变成0，等于再减去1）
# 法一
# select rank() over(order by (id-1)^1) as id,student from seat
# 法二
# 查询id和student

# 若id是偶数，减1
# 若id是奇数，加1
# 主要问题在于当总数为奇数时，最后一个id应保持不变，加1会导致空出一位。
# 解决此问题并不复杂：我们找到最后一位，让它保持不变就可以了。

# 于是得到了下面的结果：


select 
    if(id%2=0,
        id-1,
        if(id=(select count(distinct id) from seat),
            id,
            id+1)) 
    as id,student 
from seat 
order by id;
 

# 请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。
# 语法错误。。
# update sex if(sex='m','f','m')from salary 
update salary set sex = if(sex = 'm','f','m')

#ActorDirector 表：
# +-------------+-------------+-------------+
# | actor_id    | director_id | timestamp   |
# 写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
# count就是对行数进行计数，前面分组是按照actor_id,director_id 来的，也就是会分别计算 每一组别 比如 演员1，导演1 ，演员1，导演2的出现数量是多少
select actor_id,director_id
from ActorDirector
group by actor_id, director_id
having count(*)>=3

# 报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
# 细节：如果春季夏季都有售出 则不行
select s.product_id,product_name
from Sales s inner join Product p on s.product_id=p.product_id
group by s.product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31'


# 查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。
#  activity_date 要在30天内 一个用户可能在同一天有多次操作 只算一次
# !!注意！30天内可往前30还可以往后30
select activity_date as day,count(distinct user_id) as active_users   from Activity where activity_date between '2019-06-28' and '2019-07-27' group by activity_date

# 查询每个用户的注册日期和在 2019 年作为买家的订单总数。
# 查2019订单
# select buyer_id from orders where order_date >='2019-01-01'
# 查用户 注意null 报错
# select user_id,join_date,ifnull(count(buyer_id),0) 
# from Users left join (select buyer_id from orders where order_date >='2019-01-01') on buyer_id=user_id

SELECT u.user_id buyer_id, join_date, SUM(CASE YEAR(order_date) WHEN '2019' THEN 1 ELSE 0 END) orders_in_2019
FROM Users u LEFT JOIN Orders o
ON u.user_id = o.buyer_id
GROUP BY u.user_id