数据分析面试【二】—— SQL复杂语句查询

一、用户登录

1、用户表结构

1）用户的信息表user示例

id	phone
1001	13987884537

2）用户登录表login示例

id	time
1001	2020-05-13 16:46:29.029

2、取出用户最新一条的登录记录

max()函数：取出时间最大的一条记录

SELECT u.name,max(l.time)
FROM login l,user u
WHERE l.id = u.id
GROUP BY l.name
ORDER BY l.name

3、用户的累计登录次数

SELECT u.name,count(l.id) num
FROM login l,user u
WHERE l.id = u.id
GROUP BY u.id
ORDER BY num DESC

二、用户套餐订购

1、表结构

1）用户信息表user_msg

id	phone	ad	birthday
1001	13894578385	广东省	1998-04-03

2）用户订购表 user_order

userId	typeId	num
1001	1	11

3）歌曲信息表 user_order

id	name
1	蓝莲花

一个用户可以购买多个歌曲，针对一个歌曲也可以购买多次

1、查询每个用户购买的歌曲信息

多表连接最好用join on，from多表并表拼接产生笛卡尔积，数据量大时占用内存。

//方法一
SELECT m.name,s.name,o.num 
from user_order o 
join user_msg m 
on o.userId = m.id 
join service s
on o.typeId = s.id
//方法二
SELECT m.name,s.name,o.num 
from user_order o,user_msg m,service s 
WHERE o.userId = m.id and o.typeId = s.id
方法三
SELECT
(SELECT m.name from user_msg m WHERE m.id = o.userId),
(SELECT s.name from service s WHERE s.id = o.typeId),o.num
from user_order o

2、请查出每个歌曲购买的用户数量,并按照数量进行排序

SELECT s.name,count(o.userid) num
from user_order o,service s 
WHERE o.typeId= s.id 
GROUP BY s.name
ORDER BY num 

3、查询每个用户购买的歌曲数量,并按照数量降序，有多个用户名称相同

SELECT u.id,u.name,count(o.userid) num 
from user_order o,user_msg u 
WHERE o.userId= u.id 
GROUP BY u.id,u.name 
ORDER BY num desc 

4、查询购买了蓝莲花的用户信息

SELECT * 
from user_order 
WHERE typeId in (
SELECT id 
FROM service
WHERE name = "蓝莲花"
)

5、查出购买总数大于200的客户信息

SELECT userId,sum(num) s 
from user_order 
GROUP BY userId 
HAVING s>200

6、查出每个客户自己购买最多的歌曲信息

SELECT a.* 
FROM source a 
WHERE a.source = (
SELECT max(b.source) 
FROM source b 
WHERE a.s_id = b.s_id
)

7、查出每个歌曲购买最多的用户信息；

SELECT a.typeId,a.userId,a.num 
from user_order a 
WHERE a.num in (
SELECT max(num) 
from user_order b 
WHERE a.typeId = b.typeId
)

8、查出购买数量低于该首歌曲平均购买数量的用户信息

select * 
from user_order a 
WHERE num < (
SELECT avg(num) 
FROM user_order b 
WHERE a.typeId = b.typeId
)