sql题目集锦

select p.firstname,p.lastname,a.city,a.state
from person p left join address a on p.personid = a.personid;

思路1、
通过group by可以将去除重复的薪水，再进行order by可以将去重后的结果进行排序
select salary SecondHighestSalary
from employee
group by salary
order by salary desc
limit 1,1


思路2、
1、先查询出最大的薪水
2、过滤出小于最大薪水的所有数据
3、在过滤出的数据中找最大的数据
如果没有最大薪水，max()会返回null


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

# 执行limit N,M 的时候，如果没有N条记录，则会返回结果为null
# limit n,m 表示跳过n条记录，从第n+1条记录开始连续取m条记录
# group by 会根据salary进行去重，去重后的结果根据salary进行降序排列
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select salary 
      from (select salary from employee group by salary order by salary desc) as tmp
      limit N,1
  );
END

# count(distinct(s2.score)) 会先去重后再计算数量


select s1.score,count(distinct(s2.score)) `rank`
from scores s1,scores s2 
where s1.score <= s2.score
group by s1.id
order by `rank`


 select s1.id id1 , s1.score score1 , s2.id id2 , s2.score score2 
 from scores s1,scores s2 
 where s1.score <= s2.score


# 以s1的视角来看，score2 都是比 score1更大的值，因此这里按score1进行分组，每一个组的数量就是score1的是第几大的元素
# 但score1存在重复的，因此应该以id1进行分组即可
# | id1 | score1 | id2 | score2 |
# | --- | ------ | --- | ------ |
# | 1   | 3.5    | 1   | 3.5    |
# | 6   | 3.65   | 2   | 3.65   |
# | 2   | 3.65   | 2   | 3.65   |
# | 1   | 3.5    | 2   | 3.65   |
# | 6   | 3.65   | 3   | 4      |
# | 5   | 4      | 3   | 4      |
# | 4   | 3.85   | 3   | 4      |
# | 3   | 4      | 3   | 4      |
# | 2   | 3.65   | 3   | 4      |
# | 1   | 3.5    | 3   | 4      |
# | 6   | 3.65   | 4   | 3.85   |
# | 4   | 3.85   | 4   | 3.85   |
# | 2   | 3.65   | 4   | 3.85   |
# | 1   | 3.5    | 4   | 3.85   |
# | 6   | 3.65   | 5   | 4      |
# | 5   | 4      | 5   | 4      |
# | 4   | 3.85   | 5   | 4      |
# | 3   | 4      | 5   | 4      |
# | 2   | 3.65   | 5   | 4      |
# | 1   | 3.5    | 5   | 4      |
# | 6   | 3.65   | 6   | 3.65   |
# | 2   | 3.65   | 6   | 3.65   |
# | 1   | 3.5    | 6   | 3.65   |

# 把一张表作为员工表，一张表作为经理表，连接条件是员工表的managerid = 经理表的id
select e.name Employee
from employee e left join employee m on e.managerid = m.id
where e.salary > m.salary

# 通过group by 进行聚合后，用having来统计每一个分组的条数


select email as Email
from person
group by email
having count(email) > 1

#以customers作为主表来左连接orders表，如果orders表在customers表中不存在则orders的记录以空出现
#因此如果orders为空则说明这个用户不买东西


select c.name Customers
from customers c left join orders o on c.id = o.customerid
where o.customerid is null

#按部门分组找到组内的最高工资
#找到等于这个最高工资的员工
#关联部门名称表
#输出结果


select d.name Department,e.name Employee,e.salary Salary
from employee e,department d,(
# 按部门分组找到每个部门中的最高工资
select max(salary) max_salary,departmentid dept_id
from employee
group by departmentid) tmp
where e.departmentid = d.id and e.salary = tmp.max_salary and e.departmentid = tmp.dept_id

#对于某个分组内取前几名的问题：
# 1、表和自身相连，根据分组的唯一标识 + 排名字段 进行关联【on条件】 ---》 可以得到每个分组比当前数据高的所有记录
# 2、根据当前数据的唯一标识id进行分组 【group by】 ---》 分组后可以直接做count统计记录
# 3、过滤出分组后符合排名的记录 【having】 ---》 得到结果


select d.name Department,e.name Employee,e.salary Salary
from employee e left join department d on e.departmentid = d.id,
(select e1.id
# 这里的left join的条件是部门相同且工资比e1高的进行关联
from employee e1 left join employee e2 on e1.departmentid = e2.departmentid and e1.salary < e2.salary
group by e1.id
having count(distinct e2.salary) <= 2 # 去重后统计
) tmp
where e.id = tmp.id

1、先找到每个分组内id最小的记录
2、将不属于第一步查出的id的记录进行删除


delete from person where
id not in(
    select * from(
        select min(id) 
        from person 
        group by email
    ) as min_id
) 

select w1.id
from weather w1 left join weather w2
on w1.recorddate = date_add(w2.recorddate,interval 1 day)
where w1.temperature > w2.temperature;

# select e.name,b.bonus
# from employee e left join bonus b on e.empid = b.empid
# where b.bonus<1000 # 这里b.bonus<1000是要求是不为空的同时小于1000


# | name   | bonus |
# | ------ | ----- |
# | Brad   | null  |
# | John   | null  |
# | Dan    | 500   |
# | Thomas | 2000  |






select e.name,b.bonus
from employee e left join bonus b on e.empid = b.empid
where b.bonus<1000 or b.bonus is null

1、先查出每个客户下了多少订单
2、根据订单的数量按降序排布
3、通过limit取第一个元素【即下单最多的客户】


select customer_number 
from(
  select customer_number,count(*) cnt
  from orders
  group by customer_number 
) tmp
order by cnt desc
limit 0,1

# group by lat，lon表示只有lat和lon都一样的时候才是一个分组，当group by多个维度的时候，只有每个维度都相同才会归为一个分组
select lat,lon,count(*)
from insurance
group by lat,lon


# | lat | lon | count(*) |
# | --- | --- | -------- |
# | 10  | 10  | 1        |
# | 20  | 20  | 2        |
# | 40  | 40  | 1        |




#由于题目中需要找城市必须与其他投保人都不同的分组，因此这些符合条件的分组其组内只有一条记录，这时候可以取pid【不会产生歧义】


select round(sum(TIV_2016),2) tiv_2016 
from insurance
where pid in(
    select pid
    from insurance
    group by lat,lon
    having count(*)=1  
) and TIV_2015 in(
    select TIV_2015
    from insurance
    group by TIV_2015
    having count(*)>1
)

# 通过left join把两个表链接起来，链接的条件是数字相等且id大1
select distinct(l1.num) ConsecutiveNums 
from logs l1 
left join logs l2 on l1.num = l2.num and l1.id +1 = l2.id 
left join logs l3 on l1.num = l3.num and l2.id +1 = l3.id 
where l2.id is not null and l3.id is not null

# 注意，在进行group by以后每个分组内只有一条记录，后续进行order by不会影响group by的结果。
SELECT id,SUM(num) AS num
FROM (
      # 用户发出好友请求的数量
      SELECT requester_id AS id,COUNT(accepter_id ) AS num   
      FROM RequestAccepted 
      GROUP BY requester_id
      UNION ALL
      # 用户接收好友请求的数量
      SELECT accepter_id AS id,COUNT(requester_id ) AS num   
      FROM RequestAccepted 
      GROUP BY accepter_id  
      ) AS o
GROUP BY id
ORDER BY num DESC
LIMIT 1

select name
from salesperson
where sales_id not in(
      # 找到与这个公司有关的销售人员的id
      select sales_id
      from orders
      where com_id in(
            # 通过公司名找到公司的id
            select com_id
            from company
            where name = 'RED')
)

select 
t1.id id,
case 
      when t1.p_id is null then 'Root'
      when t2.id is null then 'Leaf'
      else 'Inner'
end type
from tree t1 left join tree t2 on t1.id = t2.p_id
group by t1.id  #去重


 
 select t1.*,t2.*
 from tree t1 left join tree t2 on t1.id = t2.p_id
    
  当前节点 父节点 子节点
# | id | p_id | id   | p_id |
# | -- | ---- | ---- | ---- |
# | 1  | null | 3    | 1    |
# | 1  | null | 2    | 1    |
# | 2  | 1    | 5    | 2    |
# | 2  | 1    | 4    | 2    |
# | 3  | 1    | null | null |
# | 4  | 2    | null | null |
# | 5  | 2    | null | null |

# 当子查询的结果为空的时候，max过滤出的结果也是空
select
   max(num) num
from(
      select num
      from mynumbers
      group by num
      having count(*)=1
) tmp




# 当having过滤出的结果为空的时候，什么也不会输出
select num
from mynumbers
group by num
having count(*)=1
order by num desc
limit 1

select *
from cinema
where description != 'boring' and id%2=1
order by rating desc

select sales.product_id,sales.year first_year,sales.quantity,sales.price
from sales,(
    # 找出每个产品的第一年售卖的数据
    select product_id,min(year) year
    from sales
    group by product_id) tmp
where sales.product_id = tmp.product_id and sales.year = tmp.year

# 当有的员工没有项目做的时候，通过project_id进行分组会导致有的记录会被分到null的组
select pro.project_id,avg(emp.experience_years) average_years
from employee emp left join project pro on emp.employee_id = pro.employee_id
group by project_id 


# | project_id | average_years |
# | ---------- | ------------- |
# | 1          | 3             |
# | null       | 2             |
# | 2          | 1.5           |


select pro.project_id,round(avg(emp.experience_years),2) average_years
from project pro left join employee emp on emp.employee_id = pro.employee_id
group by project_id 

# 这只是查出了在2019春季卖出过产品的记录，但【不是只在】2019年春季卖出商品的记录
select pro.product_id,pro.product_name
from product pro,sales 
where sales.sale_date>='2019-01-01' and sales.sale_date<='2019-03-31' and pro.product_id = sales.product_id




# 根据最值来查询出【只在】2019年春季卖出过的产品
select pro.product_id,pro.product_name
from product pro,(
select product_id,min(sale_date) mindate,max(sale_date) maxdate
from sales
group by product_id
having mindate>='2019-01-01' and maxdate<='2019-03-31') tmp
where pro.product_id=tmp.product_id