本文通过一系列SQL查询示例，涵盖了数据融合、薪资排名、员工比较、重复值查找、客户分析、温度变化、游戏行为分析、奖金计算、订单统计等多个场景，展示了SQL在数据处理中的强大功能。这些案例涵盖了基础的JOIN操作、子查询、聚合函数以及复杂条件筛选，对于提升SQL技能和理解数据关系具有实用价值。
目录
1083. 销售分析 II (使用 count（）和if 函数进行筛选)
175. 组合两个表
select P.firstname,p.lastname,a.city,a.state from Person P
left join Address a on p.PersonId=a.PersonId
176. 第二高的薪水
select (select distinct Salary from Employee
order by Salary desc
limit 1,1) as SecondHighestSalary.
(当Employee表里只有一条数据时，内层SQL语句查询不到数据，其返回结果是空，而外层SQL的作用是把内层的查询结果赋值给SecondHighestSalary)
181. 超过经理收入的员工
select e1.name as employee from Employee e1,
employee e2
where e1.managerid=e2.id
and e1.salary>e2.salary;
182. 查找重复的电子邮箱
select email from person
group by email
having count(*)>1
183. 从不订购的客户
select name as customers from customers
where id not in
(select customerid from orders)
197. 上升的温度
select w2.id from weather w1,weather w2
where datediff(w2.recorddate,w1.recorddate)=1
and w2.temperature>w1.temperature
select w2.id from weather w1,weather w2
511. 游戏玩法分析 I
select player_id,min(event_date) as first_login from activity
group by player_id
512. 游戏玩法分析 II
select player_id,device_id from activity
where (player_id,event_date) in
(select player_id,min(event_date) from activity group by player_id)
577. 员工奖金
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
584. 寻找用户推荐人
select name from customer
where referee_id!=2 or referee_id is null
586. 订单最多的客户
select customer_number
from orders
group by customer_number
order by count(*) desc limit 1;
595. 大的国家
select name,population,area from world
where area>3000000 or population>25000000
596. 超过5名学生的课
select class from courses
group by class
having count(distinct student)>=5
603. 连续空余座位
select distinct(c1.seat_id) from cinema c1
join cinema c2
on abs(c1.seat_id-c2.seat_id)=1
where c1.free=1 and c2.free=1
order by c1.seat_id asc
607. 销售员
select s.name from salesperson s
where s.sales_id not in(
select o.sales_id from orders o
join company c on c.com_id=o.com_id
where c.name='RED'
)
1068. 产品销售分析 I
select p.product_name,s.year,s.price
from sales s
left join product p on p.product_id=s.product_id
1069. 产品销售分析 II
select p.product_id,sum(quantity) as total_quantity from sales s
inner join product p on s.product_id=p.product_id
group by p.product_id
1075. 项目员工 I
select p.project_id,round(avg(e.experience_years),2) as average_years
from Project p
inner join employee e on p.employee_id=e.employee_id
group by project_id
1076. 项目员工II
select project_id
from project
group by project_id
having count(*)>=
all(select count(*)
from project
group by project_id
)
1082. 销售分析 I
select seller_id
from sales
group by seller_id
having sum(price)>=
all(select sum(price)
from sales
group by seller_id
)
1083. 销售分析 II (使用 count（）和if 函数进行筛选)
select buyer_id from sales s
left join product p on p.product_id=s.product_id
group by buyer_id
having count(if(p.product_name='S8',True,null))>=1 and count(if(p.product_name='iphone',True,null))=0
1084. 销售分析III
select p.product_id,p.product_name from sales s
left join product p on p.product_id=s.product_id
group by product_id
having min(s.sale_date)>='2019-01-01' and max(s.sale_date)<='2019-03-31'
1113. 报告的记录
select extra as report_reason,count(distinct post_id) as report_count from actions
where extra is not null
and datediff('2019-07-05',action_date)=1
and action='report'
group by report_reason
1141. 查询近30天活跃用户数
select activity_date as day,count(distinct user_id) as active_users
from activity
where datediff('2019-07-27',activity_date)<30
group by day
1142. 过去30天的用户活动 II
select ifnull(round(count(distinct session_id)/count(distinct user_id),2),0) as average_sessions_per_user
from activity
where datediff('2019-07-27',activity_date)<30
1148. 文章浏览 I
select distinct(author_id) as id from views
where author_id=viewer_id
order by id asc;
1179. 重新格式化部门表(行转列，sum起遍历作用）
select id,
sum(case when month='Jan' then revenue end) 'Jan_Revenue',
sum(case when month='Feb' then revenue end) 'Feb_Revenue',
sum(case when month='Mar' then revenue end) 'Mar_Revenue',
sum(case when month='Apr' then revenue end) 'Apr_Revenue',
sum(case when month='May' then revenue end) 'May_Revenue',
sum(case when month='Jun' then revenue end) 'Jun_Revenue',
sum(case when month='Jul' then revenue end) 'Jul_Revenue',
sum(case when month='Aug' then revenue end) 'Aug_Revenue',
sum(case when month='Sep' then revenue end) 'Sep_Revenue',
sum(case when month='Oct' then revenue end) 'Oct_Revenue',
sum(case when month='Nov' then revenue end) 'Nov_Revenue',
sum(case when month='Dec' then revenue end) 'Dec_Revenue'
from Department
group by id
1211. 查询结果的质量和占比
select query_name,
round(avg(rating/position),2) quality,
round(sum(if(rating<3,1,0))/count(*)*100,2) as poor_query_percentage
from queries
group by query_name
按条件统计,SUM(IF(条件，1,0))也可以用case when then else end
1251. 平均售价
select p.product_id ,round(sum(price*units)/sum(units),2) as average_price
from prices p
left join unitssold u
on p.product_id=u.product_id and u.purchase_date>=p.start_date and u.purchase_date<=p.end_date
group by p.product_id
1280. 学生们参加各科测试的次数
select s.student_id,s.student_name,s1.subject_name,count(e.subject_name) attended_exams
from students s
cross join subjects s1
left join examinations e on e.student_id=s.student_id
and e.subject_name=s1.subject_name
group by s.student_id,s1.subject_name
order by s.student_id,s1.subject_name
三表联合查询：CROSS JOIN + LEFT JOIN
1350. 院系无效的学生
select id,name from students
where department_id not in
(select id from departments)
1407. 排名靠前的旅行者
select name,ifnull(sum(distance),0) travelled_distance
from users u
left join rides r on u.id=r.user_id
group by u.id
order by travelled_distance desc,name asc
1511. 消费者下单频率
select c.customer_id,c.name
from customers c
join orders o on o.customer_id=c.customer_id
join product p on p.product_id=o.product_id
group by c.customer_id
having sum(case when month(order_date)=6 then p.price*o.quantity else 0 end)>=100 and
sum(case when month(order_date)=7 then p.price*o.quantity else 0 end)>=100
1565. 按月统计订单数与顾客数
select left(order_date,7) as month ,count(distinct order_id) as order_count,
count(distinct customer_id) as customer_count
from orders
where invoice>20
group by month
1777. 每家商店的产品价格
select product_id,
sum(case when store='store1' then price else null end) 'store1',
sum(case when store='store2' then price else null end) 'store2',
sum(case when store='store3' then price else null end) 'store3'
from products
group by product_id
group by分组会默认取第一行数据
1795. 每个产品在不同商店的价格
select * from
(select product_id,'store1' as store,store1 as price from products
union
select product_id,'store2' as store,store2 as price from products
union
select product_id,'store3' as store,store3 as price from products)a
where price is not null
扫一扫
983
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
