力扣 SQL相关题目_力扣sql题

目录

1 从不订购的客户

2 计算特殊奖金

3 删除重复的电子邮箱

4 修复表中的名字

5 按日期分组销售产品

7 丢失信息的雇员

8 每个产品在不同商店的价格

9 第二高的薪水

 逻辑法

limit关键字

窗口函数

10 上升的温度

11 股票的资本损益

12 销售分析

13 两人之间的通话次数

14 苹果和桔子

15 每月交易

16 购买了产品A、B但是没有买C的客户

17 学生们参加各科测试的次数

18 换座位

19 连续空余座位

20 应该被禁止的用户

 21 每所学校最低录取分数

22 餐馆营业额变化增长

---

1 从不订购的客户

子查询方式

select name Customers 
from Customers 
where id not in ( 
select CustomerId from orders 
)

连接查询

select c.name as Customers 
from Customers as c left join Orders as o on c.Id = o.CustomerId 
where o.Id is null

连接过程是这样的：Customers表左连接Orders表，所以Customers表是主表，将Customers表中的内容原封不动的抄下来

因为连接条件是c.id=o.customerid，数据库会利用笛卡尔积在Orders表中进行选择，将满足条件的结果添加到上面，注意这里我们是通过c.id和o.customerid进行连接的，所以两个表中的相应列现在合成了上面的一列

因为c.id=3和c.id=1在两个表中都有，所以其o.id对应的值添加在上面的表中，对于o.id没有的，数据库会直接赋值为Null，所以最终连接结果表为

我们就是在上面这个连接表中进行查询的，利用where o.Id is null将那些没有订单的客户找出来

2 计算特殊奖金

使用条件语句控制奖金列的计算方式

这里的约束条件一是员工的ID是奇数，可以用%2！=0表示，姓名不以M开头可以表示为not like 'M%'或者left(name,1)!='M',

第一种是使用MySQL中的if语句，if(条件，语句1，语句2)如果条件成立执行语句1，否则执行语句2

select employee_id,
if (employee_id%2!=0 and name not like 'M%',salary,0) as bonus
from employees
order by employee_id

第二种是使用case when then else end语句

select employee_id,
case 
when employee_id%2!=0 and name not like 'M%'
then salary
else 0
end as bonus
from employees
order by employee_id

3 删除重复的电子邮箱

 

 delete的另一种用法

DELETE p1
from person p1,person p2
where p1.email=p2.email and p1.id>p2.id

4 修复表中的名字

 常用的字符串处理函数

concat():字符串连接

left(s,n):获取字符串左边第n个字符

lower()、upper():字符串大小写转换

substring(s,n):从字符串s第n个位置开始截取

select user_id,concat(upper(left(name,1)),lower(substring(name,2))) as name
from users
order by user_id

5 按日期分组销售产品

group_concat官方DOC

 将一组非NULL结果以字符串连接的形式返回，如果有NULL值直接返回NULL，可指定连接元素的排列和分隔符，默认是升序和以‘，’分割

select sell_date,
count(distinct product) num_sold,
group_concat(distinct product) products
from activities
group by sell_date
order by sell_date

7 丢失信息的雇员

 

 

 两种解决方式

select temp.employee_id
from 
(select a.employee_id
from employees a left join salaries b on a.employee_id=b.employee_id
where b.salary is NULL
union
select b.employee_id
from employees a right join salaries b on a.employee_id=b.employee_id
where a.name is NULL) as temp
order by temp.employee_id

union可以将两个结果表去重的合并在一起，那么先寻找姓名缺少再寻找工资缺失，然后通过union进行去重合并就能得到所有信息缺失的ID

select 
    employee_id 
from 
    (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

将两个表合并到一起，然后根据ID分组，分组中ID数量为1的表示有信息缺失

8 每个产品在不同商店的价格

 

 

 宽表变长表

select product_id,'store1' as store,store1 as price
from products
where store1 is not NULL
union all
select product_id,'store2' as store,store2 as price
from products
where store2 is not NULL
union all 
select product_id,'store3' as store,store3 as price
from products
where store3 is not NULL

另外补充长表变宽表

select product_id
case when store='store1' then price else NULL end as 'store1',
case when store='store2' then price else NULL end as 'store2',
case when store='store3' then price else NULL end as 'store3'
from products
order by product_id

9 第二高的薪水

 

 逻辑法

第二就是小于第一的最大值

select if(max(distinct salary) is not NULL,max(distinct salary),NULL) as SecondHighestSalary
from Employee
where salary<(
    select max(salary)
    from Employee
)

limit关键字

MySQL中imit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据。

select ifnull((
    select distinct salary
    from Employee
    order by salary desc
    limit 1 offset 1
),NULL) as SecondHighestSalary

窗口函数

#外面select可以处理NULL值
select (
    select salary 
    from 
    (
        #利用窗口函数获取排名信息表
        select salary, 
        #dense_rank当有并列排名时
        #下一个排名是接着上一个的排名
        #就像这样：1，1，2
        dense_rank() over (order by salary desc) rn
        from Employee
    ) as t 
    #获取第二高，只返回一个
    where t.rn=2 limit 1
) SecondHighestSalary

10 上升的温度

 

 datediff(日期1，日期2）返回两个日期相差的天数

# Write your MySQL query statement below
select a.id 
from weather as a
#当天温度比前一天高
where a.temperature>(
    #获取前一天的温度
    select b.temperature
    from weather as b
    where datediff(a.recorddate,b.recorddate)=1
)
order by a.id

进行SQL优化，从笛卡尔积中取结果

SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;

11 股票的资本损益

 

 多表构造连接方式

select a.stock_name,b.sell_money-a.buy_money as capital_gain_loss
from 
(
    ##创建每只股票购买所花的钱数
    select stock_name,sum(price) as buy_money
    from stocks
    where operation='Buy'
    group by stock_name
) as a
inner join 
(
    ##创建每只股票出售所获得的钱数
    select stock_name,sum(price) as sell_money
    from stocks
    where operation='Sell'
    group by stock_name
) as b on a.stock_name=b.stock_name

case语句

select stock_name,
sum(
    case operation
    when 'Buy' then -price
    else price
    end
) as capital_gain_loss
from stocks
group by stock_name

12 销售分析

 

SELECT s.product_id, product_name
FROM Sales s
Left 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'

13 两人之间的通话次数

 

 A给B打，B也给A打的话，将回拨电话记录列进行交换，只以打电话中的一个人为中心进行分组统计

least()函数用来寻找一些值中最小的一个 ,group by 除了可以指定列名还可以使用表达式

select from_id as person1,
to_id as person2,
count(from_id) as call_count,
sum(duration) as total_duration
from calls
group by least(from_id,to_id),greatest(from_id,to_id)

14 苹果和桔子

 

聚合函数是用在整个分组上的，所以可以在其中控制分组中每一项的具体表达式

select sale_date,sum(
    case fruit
    when 'apples' then sold_num
    else -sold_num
    end
) as diff
from sales
group by sale_date
order by sale_date

15 每月交易

 带有条件的计数和求和，注意count中的or NULL条件

select 
left(trans_date,7) as month,
country,
count(state) as trans_count,
count(state='approved' or NULL) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from transactions
group by country,year(trans_date),month(trans_date)

16 购买了产品A、B但是没有买C的客户

 

instr(A,B)可以在字符串A中搜索B如果包含B返回B首次出现的位置，否则返回0

# Write your MySQL query statement below
select customer_id,customer_name
from customers
where customer_id in (
#统计买了产品A、B但是没有买C的ID
select a.customer_id
from (
#统计每个用户的购买情况
select customer_id,group_concat(distinct product_name) as times
from orders
group by customer_id
) as a 
#用instr进行筛选
where instr(a.times,'C')=0 and instr(a.times,'A')!=0 and instr(a.times,'B')!=0
)
order by customer_id

17 学生们参加各科测试的次数

 

 

 count(列名）时如果这个列所有的值都为NULL返回0，先交叉连接构建出学生和学科的对应关系然后左外连接考试信息表，进行分组，在每组进行统计

SELECT a.student_id, a.student_name, b.subject_name, COUNT(e.subject_name) AS attended_exams
FROM Students a CROSS JOIN Subjects b
    LEFT JOIN Examinations e ON a.student_id = e.student_id AND b.subject_name = e.subject_name
GROUP BY a.student_id, b.subject_name
ORDER BY a.student_id, b.subject_name

18 换座位

 直接改变ID

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
 

19 连续空余座位

 利用join进行表和自身的笛卡尔积，然后筛选出连续的、都是空闲的座位

select distinct a.seat_id
from cinema a join cinema b on
#连续的座位
abs(a.seat_id-b.seat_id)=1 and 
#都是空闲的
a.free=1 and b.free=1
order by a.seat_id

20 应该被禁止的用户

 

SELECT DISTINCT a.account_id AS account_id
#隐式自连接
FROM LogInfo a, LogInfo b
WHERE 
#某个用户
a.account_id = b.account_id  
#异地登录
AND a.ip_address != b.ip_address 
#一个还没下线
AND a.logout <= b.logout  
#另外一个就进行了登录
AND b.login <= a.logout 

 21 每所学校最低录取分数

 

 连接中可以使用不等条件

select 
school_id, 
#取最低分数，如果为null，则输出-1
ifnull(min(score), -1) as score 
from 
Schools s left join Exam e 
#容量需大于分数段学生数量
on  capacity >= student_count 
group by school_id

22 餐馆营业额变化增长

 滑动窗口可以控制窗口的大小

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

elect
    visited_on,
    amount,
    average_amount
from (
select
visited_on,
#按照访问日期进行排序
row_number() over(order by visited_on) as rk,
#过去7天用rows指定
#最外一层的sum是组内求和
sum(sum(amount)) over(order by visited_on rows between 6 preceding and current row) as amount,
round(avg(sum(amount)) over(order by visited_on rows between 6 preceding and current row), 2) as average_amount
from Customer
group by visited_on
) as base
where rk >= 7
order by visited_on