sql 按字段分组后没有数据怎么列出分组_自学SQL网_学习简单部分

另一个SQL刷题圣地：

自学SQL网(教程 视频 练习全套)​xuesql.cn

整理了一部分，前部分比较简单。

---

进阶部分需要money，就没接触。希望可以有朋友赞赏一下，给点动力呀。

欢迎吐槽~

---

lesson 07 外连接

buildings 、 employees 数据表

1.找到所有有雇员的办公室（buildings）名字

select distinct building_name

from buildings b left join employees e

on e.building = b.building_name

where building is not null;

2.找到所有办公室里的所有角色（包含没有雇员的），并做唯一输出（DISTINCT）

select distinct building_name,role

from buildings b left join employees e

on e.building = b.building_name;

3.找到所有有雇员对的办公室（buildings）和对应的容量

select distinct building_name,capacity

from buildings b left join employees e

on e.building = b.building_name

where building is not null;

Lesson 8: 关于特殊关键字 NULLs

buildings 、 employees 数据表

1.找到雇员里还没有分配办公室的(列出名字和角色就可以)

select name,role

from employees

where building is null;

2.找到还没有雇员的办公室

select building_name

from buildings b left join employees e

on b.building_name = e.building

where name is null;

Lesson 9: 在查询中使用表达式

Movies 、 Boxoffice 数据表

1.【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

select id,title,(domestic_sales+international_sales)/1000000

from movies m left join boxoffice b

on m.id = b.movie_id;

2.【计算】列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

select id,title,rating*10

from movies m left join boxoffice b

on m.id = b.movie_id;

3.【计算】列出所有偶数年份的电影，需要电影ID,名字和年份

select id,title,year

from movies m left join boxoffice b

on m.id = b.movie_id

where year%2 == 0;

4.【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

select title,(domestic_sales+international_sales)/length_minutes

from movies m left join boxoffice b

on m.id = b.movie_id

where director = 'John Lasseter'

order by (domestic_sales+international_sales)/length_minutes desc

limit 3;

Lesson 10: 在查询中进行统计I (Pt. 1)

Employees 数据表

1.【统计】找出就职年份最高的雇员(列出雇员名字+年份）

select name,years_employed

from employees

order by years_employed desc

limit 1;

2.【分组】按角色(Role)统计一下每个角色的平均就职年份

select role,avg(years_employed)

from employees

group by role;

3.【分组】按办公室名字总计一下就职年份总和

select building,sum(years_employed)

from employees

group by building;

4.【难题】每栋办公室按人数排名,不要统计无办公室的雇员

select building,count(*)

from employees

where building is not null

group by building

order by count(*) desc;

Lesson 11: 在查询中进行统计II (Pt. 2)

Employees 数据表

1.【统计】统计一下Artist角色的雇员数量

select count(name)

from employees

group by role

having role = 'Artist';

2.【分组】按角色统计一下每个角色的雇员数量

select role,count(name)

from employees

group by role;

3.【分组】算出Engineer角色的就职年份总计

select sum(years_employed)

from employees

where role = 'Engineer'

group by role;

4.【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

select role,

case when building is null then 0 else 1 end as have_b

,count(name)

from employees

group by role,have_b;

Lesson 12: 查询执行顺序

Movies 、 Boxoffice 数据表

1.【复习】统计出每一个导演的电影数量（列出导演名字和数量）

SELECT director,count(title) FROM movies group by director;

2.【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)

SELECT director,sum(domestic_sales+international_sales) FROM movies m join boxoffice b

on m.id = b.movie_id

group by director;

3.【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)

select director,sum_a,count_a,sum_a/count_a

from

(

SELECT director,sum(domestic_sales+international_sales) as sum_a, count(title) as count_a

FROM movies m join boxoffice b

on m.id = b.movie_id

group by director

having count(title) <> 1)a

order by sum_a/count_a desc

limit 1;

4.【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

SELECT

(

SELECT

(Domestic_sales+International_sales) as total_sale

FROM movies

left join boxoffice

on Movies.id - Info Teknologi Terkini = boxoffice.movie_id

order by total_sale desc limit 1

)

-

(Domestic_sales+International_sales) as sale_diff,

title

FROM movies

left join boxoffice

on movies.id = boxoffice.movie_id

order by sale_diff desc;