牛客 SQL基础查询

->前往做题<-

一、查询结果去重 distinct

select distinct university from user_profile;

二、查询结果排序 order

1. 查找后按照某一列排序

select device_id, age from user_profile order by age asc;
-- 按照age列升序排列

2. 查找后多列排序
   题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

select device_id,gpa,age from user_profile order by gpa,age asc;
-- 先按照gpa升序排列，gpa相同时再按照age升序排列

或者

select device_id,gpa,age from user_profile order by gpa asc,age asc;

3. 多列降序排列

select device_id,gpa,age
from user_profile
order by gpa desc,age desc;

三、条件查询 where

基本操作符练习

题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

select device_id, university 
from user_profile 
where university='北京大学';

题目：选出年龄>=24的人，返回 device_id, gender, age, university

select device_id, gender, age, university 
from user_profile 
where age>=24;

【区间查询】题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。

-- 使用 between 关键字
select device_id,gender,age 
from user_profile 
where age between 20 and 23;

-- 不使用 between 关键字
select device_id,gender,age 
from user_profile 
where (age >= 20 and age <= 23)

【排除查询】题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据。

-- 用 !=
select device_id, gender, age, university
from user_profile
where university != '复旦大学'

-- 用 not
select device_id, gender, age, university
from user_profile
where not university = '复旦大学'

-- 用 not in
SELECT device_id, gender,age,university 
FROM user_profile 
WHERE university NOT IN ('复旦大学')

【空值过滤】题目：取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

-- 解法一：null
select device_id,gender,age,university
from user_profile
where age is not NULL;

-- 解法二：!=
select device_id,gender,age,university
from user_profile
where age !='';

高级操作符练习

1、and、or

select device_id ,gender,age,university,gpa 
from user_profile 
where gpa>3.5 and gender = 'male'

select device_id,gender,age,university,gpa
from user_profile
where university='北京大学' or gpa>3.7

2、in
题目：找到学校为北大、复旦和山大的同学

select device_id,gender,age,university,gpa 
from user_profile
where university in('北京大学','复旦大学','山东大学');

3、多重条件
题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

select device_id,gender,age,university,gpa
from user_profile
where (gpa>3.5 and university='山东大学') or (gpa>3.8 and university='复旦大学')

4、like
查看学校名称中含“北京”的用户

select device_id,age,university
from user_profile
where university like '%北京%'

计算函数

1、最大值max
题目：查找GPA最大值，返回这个最大值

# 方法1 max
select max(gpa) as gpa		--as是对字段重命名
from user_profile
where university='复旦大学';

# 方法2 降序排序后取出第一条
select gpa
from user_profile
where university='复旦大学'
order by gpa desc limit 1

2、计数count、求均值avg
题目：查看男性用户有多少人以及他们的平均gpa（保留一位小数）是多少，用以辅助设计相关活动，请你取出相应数据。

select count(device_id), round(avg(gpa), 1)
from user_profile
where gender = 'male'

四、分组查询

知识点：

• group by
• having

1、题目：请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

用户信息表：user_profile
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）

SELECT
	gender,			 -- 只能出现分组依据列
	university,		 -- 只能出现分组依据列
	count( device_id ),
	avg( active_days_within_30 ),
	avg( question_cnt ) 
FROM
	user_profile 
GROUP BY
	gender,			-- 分组依据列
	university		-- 分组依据列

使用了 group by 之后，聚合函数分别对每个组起作用，如按 性别（gender）分组的，count( device_id )就会对相同性别的组计算 device_id 的数量。

注意：带有GROUP BY 子句的SELECT语句的查询列表中只能出现【分组依据列】或【统计函数】，因为分组后每个组只返回一行结果。

2、题目：请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
跳转到题目

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4.0	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

SELECT
	university,
	avg( question_cnt ) AS avg_question_cnt,
	avg( answer_cnt ) AS avg_answer_cnt 
FROM
	user_profile 
GROUP BY
	university 
HAVING				-- 注意与where区别，having用于group by后
	avg( question_cnt ) < 5 
	OR avg( answer_cnt ) < 20

3、查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列
跳转到题目

问题分解：
限定条件：无；
不同大学：按学校分组group by university
平均发帖数：avg(question_cnt)
升序排序：order by avg_question_cnt

SELECT
	university,
	avg( question_cnt ) AS avg_question_cnt
FROM
	user_profile 
GROUP BY
	university 
ORDER BY			-- 返回结果排序
	avg_question_cnt ASC

五、联表查询

题目：查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
跳转到题目

表1（答题情况表）：question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	114	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

表2（用户信息表）：user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4.0	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

要求输出：

注意：两张表通过 “device_id” 属性相关联。
题解：查询第二张表中“浙江大学”的device_id在第一张表的记录，即两张表公共的“device_id”，使用 inner join...on...

-- 方法1：使用join
select 
    q.device_id as device_id,
    q.question_id as question_id,
    q.result as result
from question_practice_detail as q 
inner join user_profile as u
on q.device_id = u.device_id and u.university = '浙江大学'

-- 方法2：不使用join
select device_id, question_id, result
from question_practice_detail
where device_id in (
    select device_id from user_profile
    where university='浙江大学'
)

题目：统计每个学校的答过题的用户的平均答题数
跳转到题目

问题分解：
限定条件：无；
每个学校：按学校分组，group by university
平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。
表连接：学校和答题信息在不同的表，需要做连接

select 
    u.university,
    count(q.question_id) / count(distinct q.device_id) as avg_answer_cnt
from question_practice_detail as q 
inner join user_profile as u
on q.device_id = u.device_id 
group by u.university