SQL查询实例

Posted on | Post modified: | In |
Words count in article: 748

分组计算

在使用count, sum等聚合函数时，如果不加限制就是统计所有结果行，但有时这不是预期的结果。如果要分类统计的话，需要使用group by将结果分类，这样它们便会按照分类进行计算。

如统计不同学校不同型别的人数：

1
2
3
select gender,university, count(id) as user_num,
from user_profile
group by university,gender   -- 将结果按照学校和型别分组，这样聚合函数才会以组为单位计算

分组过滤(聚合函数作为约束需要用having)

如果要过滤聚合函数计算出来的结果，只能用having子句，不能使用where。同时注意having子句需要放在group by之后。

1
2
3
4
select university, avg(question_cnt) as ac, avg(answer_cnt) as qc
from user_profile
group by university
having ac < 5 or qc < 20

多表查询

查询需要的约束条件在另一个表中，使用select子句返回约束结果。

1
2
3
4
5
6
7
select device_id, question_id, result
from question_practice_detail
where device_id in 
(
    select device_id from user_profile 
    where university = '浙江大学'
)

三表查询

需要查询的数据在表之间互相依赖，使用join将表先连接起来再查询。

1
2
3
4
5
6
select university, difficult_level, (count(d.question_id) / count(distinct(u.device_id)))
from user_profile as u
join question_practice_detail as q 
on u.device_id = q.device_id
join question_detail d on d.question_id = q.question_id
group by university, difficult_level;
1
2
3
4
5
6
7
8
9
10
11
SELECT university, difficult_level, (count(q.question_id) / count(distinct(q.device_id)))
FROM user_profile u

join question_practice_detail q 
on u.device_id = q.device_id
join question_detail d
on q.question_id = d.question_id   -- 连成一个表

where university = '山东大学'        -- 现在在上面合成的表中查询
group by difficult_level
order by difficult_level

多表查询（约束为func）

获取每个部门中当前员工薪水最高的相关信息 : 不仅需要获取部门内最高薪水，还需要获取最高薪水的员工是谁。

1
2
3
4
5
6
7
8
9
10
11
12
-- 这题的关键是需要确定最大薪水的员工是谁

SELECT d.dept_no,d.emp_no,s.salary AS maxSalary
    FROM dept_emp d
    INNER JOIN salaries s
    ON d.emp_no = s.emp_no
    WHERE (d.dept_no,s.salary) -- 选出员工薪水等于 临时表中最大薪水的记录
        IN (SELECT d.dept_no, MAX(s.salary) AS maxsal  -- 临时表用于查询最大薪水是多少
            FROM dept_emp d INNER JOIN salaries s
            ON d.emp_no = s.emp_no
            GROUP BY d.dept_no)
    ORDER BY d.dept_no ASC;

联合查询

满足任意一种条件的都可以，并且不去重

1
2
3
4
5
SELECT device_id, gender, age, gpa from user_profile
where university = '山东大学' 
union ALL
SELECT device_id, gender, age, gpa from user_profile
where gender = 'male'

结果分类

对于查询结果，按照是否满足某个条件或在某个范围内 分别对应不同的结果。

对与二分类，可方便的使用if，case when也可以

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 统计大于和小于25岁人的数量
SELECT
if (age >= 25, '25岁以及上', '25岁以下') as age_cnt, count(device_id) as number
from user_profile
group by age_cnt;

SELECT
case 
when age >= 25 then '25岁以及上'
else '25岁以下' 
END as age_cnt,
count(device_id) as number 
from user_profile
group by age_cnt;
1
2
3
4
5
6
7
8
9
-- 统计每个人分别属于哪个年龄段
SELECT device_id, gender, 
case 
when age < 20 then '20岁以下'
when age >= 20 and age <= 24 then '20-24岁'
when age >= 25 then '25岁以上'
else '其他'
END as age_cnt
from user_profile;