写一个SQL吧。student(id, name, sex, age), book(id, name, stu_id(借阅学生id)),输出一下前十个18周岁男孩中借书最多的名字以及书本数量
因为没有数据,全部数据都是手动写的,为了不必要的麻烦就改为前三!
建表
-- 表 student
create table student(
id string,
name string,
sex string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 表:book
create table book(
id string,
name string,
stu_id int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
数据
数据ex1_data_book:书籍表book
1,book_1,1
2,book_2,1
3,book_3,1
4,book_4,1
5,book_5,1
6,book_1,7
7,book_2,7
8,book_3,7
9,book_4,7
10,book_1,8
11,book_2,8
12,book_3,8
13,book_1,10
14,book_2,10
数据ex1_data_stu:学生表student
1,zs,b,18
2,ls,b,19
3,qw,g,18
4,er,g,19
5,ty,b,18
6,ui,b,19
7,op,b,18
8,sa,b,18
9,df,b,20
10,gh,b,18
11,jk,b,18
12,lk,b,18
13,zx,b,18
14,cx,b,18
15,vb,b,18
16,nm,b,18
17,mn,g,19
19,xx,g,20
20,oo,g,18
分析
根据题目要求,先筛选出18岁男生
-- 筛选出符合18周岁、男孩子特征的人群
select id,name boy from student where age=18 and sex='b';
-- 结果
OK
id boy
1 zs
5 ty
7 op
8 sa
10 gh
11 jk
12 lk
13 zx
14 cx
15 vb
16 nm
借书最多的名字(人名),以及借书数量,
每本书都会记录一个stu_id,那么也就是说在book表中一个stu_id代表一本书,
所以直接按stu_id分组,count统计stu_id个数即书本数量。
select stu_id,count(1) book_num from book group by stu_id;
-- 结果
stu_id book_num
1 5
7 4
8 3
10 2
答案
sql_1:两个job
select boy,book_num
from
(select id,name boy from student where age=18 and sex='b')stu
join
(select stu_id,count(1) book_num from book group by stu_id)t_book
on stu.id = t_book.stu_id
limit 3;
-- 结果
boy book_num
zs 5
op 4
sa 3
sql_2:两个job
with tmp as(
select stu.boy,bk.b_num,row_number() over(order by bk.b_num desc)t_num
from
(select id,name boy from student where age=18 and sex='b')stu
join
(select stu_id,count(1)b_num from book group by stu_id)bk
on stu.id = bk.stu_id
)
select * from tmp where t_num <= 3;
-- 结果
OK
boy b_num t_num
zs 5 1
op 4 2
sa 3 3
结束语
可能还有其他写法,没有过多深究,有其他写法的朋友,可以留言讨论