由一次问题引发的对SQL的强烈求生欲

由一次问题引发的对SQL的强烈求生欲

困而学,学而知 这天,我还在工位上思考怎么写一个不被发现bug。项目经理悄悄来到我身后,一直手拍到我肩膀上,道:帮我统计一下...说了一堆我需要统计的数据。听完之后的我黑人问号,心想这些咋个统计。好吧,我承认我自己菜,谁叫我也是很久不使用sql的高级查询了。考验查询新知识的时候到了。

先准备这次要完成的需求的数据环境吧。

**数据环境:**图书馆中1000本书,每本书的id不同、书本分类和存库时间。图书馆的书可以借出,每次借出都会记录借出时间、图书id和借书人。

通过这个数据环境,我们就可以创建两张表library和borrow。

create table library (
    id int auto_increment primary key,
    book varchar(50),
    classify varchar(50),
    storeTime timestamp
);
create unique index library_index on library(id(50));
书id书名分类存库时间
idbookclassifystoreTime
create table borrow(

    borrowId int not null,
    foreign key (borrowId) references library(id) on delete cascade on insert cascade,
    borrowTime timestamp,
    person varchar(50)
);
书id借书时间借书人
borrowIdborrowTimeperson

需求一 统计一周内有那些人借小说

分析: 要查找一周内(7天)有多少人借了书,首先需要找到这7天内有多少人借了那些书,然后通过借阅表的borrowId和图书表的id的外键关系,过滤掉这7天借阅书籍中不是小说的书籍。这样,就可以得出这一周内有哪些人借小说。那么上代码。

select bo.* from (select * from borrow b where date_sub(curdate(), interval 7 day) <= date(b.borrowTime) ) bo where bo.borrowId in (select l.id from library l where l.classify = '小说');

curdate()当前日期。e.g. 2018-12-05

date_sub(date,INTERVAL expr type)从日期减去指定时间间隔,上面的date_sub(curdate(), interval 7 day)标示当前日期减7天,也就是7天前。

现在,换一种解法,连接两个borrow和library表,查询两个borrowid和id相同,7天前且分类是小说的数据。

select b.*, l.classify from borrow b inner join library l on b.borrowId = l.id and date_sub(curdate(), interval 7 day) <= date(b.borrowTime) and l.classify = '小说';

...inner join...on...连接两个表,返回符合两个表的数据,on后面跟查询条件。

需求二 有哪些书被同一个人重复借,借了多少次,书名具体是什么

分析:先统计出借阅表中借阅人对应书籍的借阅次数。然后联合表查询出对应的书名。

select l.book, b.* from library l inner join (select borrowId, person, count(1) from borrow group by borrowId, person) b on b.borrowId = l.id;

其实这句sql不只是统计了重复借阅的次数,她统计了具体借阅的次数,如果要统计重复借阅次数,还要在结尾加一个条件。

想了想,发现目前也就是这两个需求,我也决定就先这俩了。后面如果还有,我也会继续添加。

转载请说明出处

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://baozi.fun/2018/12/05/由一次问题引发的对SQL的强烈求生欲

Buy me a cup of coffee ☕.