Sql多表查询

1、子查询

浙江大学用户题目回答情况

现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据。

select
    qpd.device_id,
    qpd.question_id,
    qpd.result
from
    question_practice_detail as qpd
    inner join user_profile as up on up.device_id = qpd.device_id
    and up.university = '浙江大学'
order by
    question_id
1
2
3
4
5
6
7
8
9
10

select
    device_id,
    question_id,
    result
from
    question_practice_detail
where
    device_id in (
        select
            device_id
        from
            user_profile
        where
            university = '浙江大学'
    )
order by
    question_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2、链接查询

统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

select
    university,
    count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from
    question_practice_detail as qpd
    inner join user_profile as up on qpd.device_id = up.device_id
group by
    university
1
2
3
4
5
6
7
8

统计每个学校各难度的用户平均刷题数

运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。

//每个学校：按学校分组group by university
//不同难度：按难度分组group by difficult_level
//平均答题数：总答题数除以总人数count(qpd.question_id) / //count(distinct qpd.device_id)
//来自上面信息三个表，需要联表，up与qpd用device_id连接，
//qd与qpd用question_id连接。
//平均值精度：保留4位小数round(x, 4)
select
    university,
    difficult_level,
    round(
        count(qpd.question_id) / count(distinct qpd.device_id),
        4
    ) as avg_answer_cnt
from
    question_practice_detail as qpd
    left join user_profile as up on up.device_id = qpd.device_id
    left join question_detail as qd on qd.question_id = qpd.question_id
group by
    university,
    difficult_level

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

统计每个用户的平均刷题数

运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据。

SELECT
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT (t2.device_id)) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3、组合查询

查找山东大学或者性别为男生的信息

现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

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'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19