0. 数据库开发/数据分析面试笔试题整理记录

1. 图书租赁

图书租赁公司AA 在全国范围内开设了多家商业图书馆，为顾客提供付费图书租借服务。顾客统一注册，同一个顾客编号可全国通用。以下是该公司运营信息的数据表定义：

1. 门店信息表（表名T_shop）

字段名称	数据类型	字段含义
shop_id	int	门店编号
shop_name	varchar	门店名称
which_city	varchar	所在城市(Beijing表示北京，Shanghai表示上海，Shenzhen表示深圳)

2. 作者信息表（表名T_author）

字段名称	数据类型	字段含义
author_id	int	作者编号
author_name	varchar	作者名称

3. 出版社信息表（表名T_publisher）

字段名称	数据类型	字段含义
publisher _id	int	出版社编号
publisher_name	varchar	出版社名称

4. 图书信息表（表名T_book）

字段名称	数据类型	字段含义
book _id	int	图书编号
book_name	varchar	图书名称
which_shop	int	所在门店的编号
which_author	int	作者的编号
which_ publisher	int	出版社的编号
cost_num	float	图书采购成本
price_num	float	图书租借单日价格

5. 顾客信息表（表名T_guest）

字段名称	数据类型	字段含义
guest _id	int	顾客编号
guest_name	varchar	顾客姓名
which_gender	boolean	性别，1为男，0为女
register_date	date	注册日期，格式为’yyyy-mm-dd’

6. 租赁信息表（表名T_rental）

字段名称	数据类型	字段含义
rental _id	int	租赁编号
which_guest	int	顾客编号
which_book	int	图书编号
rental_date	date	租赁日期，格式为’yyyy-mm-dd’
rental_days	int	租赁天数

请为以下任务编写SQL查询代码

1. 查询深圳的门店总数

select count(distinct shop_id) as 深圳门店总数
from T_shop 
where which_city = 'Shenzhen'

2. 列出北京和上海所有门店中的图书名称中含有“福尔摩斯”的图书编号

select a.book_id
from T_book a 
inner join T_shop  b 
on a.which_shop = b.shop_id	 
where b.which_city in ('Beijing','Shanghai')
and a.book_name like '%福尔摩斯%'

3. 列出各门店的编号、名称、2019年6月的营业额（按租赁金额计算），各门店按营业额从大到小排序。

select b.shop_id,b.shop_name,isnull(sum(price_num*rental_days),0) as 营业额
from T_book a
left join T_shop b
on a.which_shop = b.shop_id
left join T_rental c
on a.book_id = c.which_book and left(rental_date,7) = '2019-06'
group by b.shop_id,b.shop_name
order by sum(price_num*rental_days) desc

4. 列出在2019年被顾客租赁超过1000次的中信出版社的图书编号、图书名称、作者名称、出版社名称、所在门店、租赁次数，按照租赁次数从大到小排列。

select a.which_book,b.book_name,c.author_name,d.publisher_name,e.shop_name,count(distinct a.rental_id) as 租赁次数 
from T_rental a 
left join T_book  b 
on a.which_book  = b.book_id 
left join T_author c 
on b.which_author = c.author_id
left join T_publisher d 
on b.which_publisher = d.publisher_id
left join T_shop e 
on b.which_shop = e.shop_id
where Year(a.rental_date) = '2019'
group by a.which_book,b.book_name,c.author_name,d.publisher_name,e.shop_name
having count(distinct a.rental_id)>1000
order by count(distinct a.rental_id) desc

5. 列出上海各门店至今没有产生收入的图书的采购成本总额，显示门店编号、门店名称、成本总额，按成本从大到小排列

select a.shop_id,a.shop_name,isnull(sum(b.cost_num),0) as 采购成本总额
from T_shop  a
left join T_book b
on a.shop_id = b.which_shop
where a.which_city = 'shanghai'
and not exists (select * from T_rental o where b.book_id = o.which_book )
group by a.shop_id,a.shop_name
order by sum(b.cost_num)  desc

2. 最大连续登陆天数

假设有张登录日志表记录某款游戏的登录情况，查询这张表中每个玩家的最大连续登陆天数

--建表
if object_id('LogInfo','u') is not null drop table LogInfo
go
create table LogInfo(
    UserName varchar(20)
    ,LogTime  datetime
)
go
insert into LogInfo values
('Bob'      ,'2023-11-01 00:14:00'),
('Bob'      ,'2023-11-02 01:12:00'),
('Bob'      ,'2023-11-03 11:22:00'),
('Bob'      ,'2023-11-03 12:32:00'),
('Jack'     ,'2023-11-02 08:32:00'),
('Jack'     ,'2023-11-04 10:59:00'),
('Charlie'  ,'2023-11-12 09:20:00'),
('Charlie'  ,'2023-11-13 12:37:00'),
('Charlie'  ,'2023-11-14 09:20:00'),
('Charlie'  ,'2023-11-15 11:37:00'),
('Charlie'  ,'2023-11-17 02:07:00'),
('Helen'    ,'2023-11-03 10:07:00'),
('Helen'    ,'2023-11-04 16:35:00'),
('David'    ,'2023-11-02 10:29:00'),
('David'    ,'2023-11-08 11:03:00'),
('Isabel'   ,'2023-11-02 11:45:00'),
('Edward'   ,'2023-11-27 12:19:00'),
('Edward'   ,'2023-11-28 12:54:00'),
('Alice'    ,'2023-11-02 14:29:00'),
('Alice'    ,'2023-11-04 19:29:00'),
('Oliver'   ,'2023-11-23 00:26:00'),
('Oliver'   ,'2023-11-25 16:33:00'),
('Oliver'   ,'2023-11-26 16:40:00'),
('Oliver'   ,'2023-11-26 18:40:00'),
('Nancy'    ,'2023-11-03 11:18:00'),
('Nancy'    ,'2023-11-04 12:18:00'),
('Nancy'    ,'2023-11-05 08:03:00'),
('Katherine','2023-11-03 13:18:00'),
('Patricia' ,'2023-11-03 14:33:00'),
('Frank'    ,'2023-11-14 07:51:00'),
('Frank'    ,'2023-11-16 07:51:00'),
('Frank'    ,'2023-11-17 04:21:00'),
('Frank'    ,'2023-11-18 19:22:00'),
('Sarah'    ,'2023-11-04 08:11:00'),
('Sarah'    ,'2023-11-10 21:34:00'),
('George'   ,'2023-11-04 09:27:00'),
('Victoria' ,'2023-11-05 11:51:00'),
('Victoria' ,'2023-11-06 15:26:00'),
('Richard'  ,'2023-11-04 15:07:00'),
('Lawrence' ,'2023-11-05 10:27:00'),
('Thomas'   ,'2023-11-06 09:03:00'),
('Margaret' ,'2023-11-09 10:20:00')
 go
--查询
with t as (select distinct username ,convert(varchar(10),logtime,112) as logdate  from loginfo )

select username,max(连续登陆天数) as 最大连续登陆天数 from (
select UserName,count(* )  as  连续登陆天数
from (select *
        ,row_number() over(partition by UserName order by logdate ) as rnk
        ,cast (logdate as int ) - row_number() over(partition by UserName order by logdate ) as diff
        from t  )  a
group by username ,diff  ) a
group by username
order by username