DAY01 SQL训练

DQThinkSpace

已于 2023-12-11 10:34:01 修改

阅读量53

点赞数 1

文章标签： mysql

于 2023-10-29 11:45:01 首次发布

本文链接：https://blog.csdn.net/weixin_44978991/article/details/134100804

版权

前言

提示：这里可以添加本文要记录的大概内容：

最近，在训练牛客题库SQL必知必会，这里写练习笔记作为积累。

每日一题，2023年10月29日，上午。

提示：以下是本篇文章正文内容，下面案例可供参考

一、分析思路

Step1：题意明确：

了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

Step2：问题分解：

1）限制条件：用户信息表、答题详细表

2）日期：使用month函数

3）总的题目数：count函数

4）正确题目数：sum函数

5）where过滤

6）分类聚合：group by

Step3：细节问题：

1）细节1：

`month(qpd.date) = 8` 是一个SQL查询中的条件表达式，它用于从日期数据中提取月份并检查是否等于8。让我详细解释它的含义和相关的知识点：

1. **`month(qpd.date)`**：这部分使用了一个日期函数 `month()`，它的作用是从给定日期中提取月份部分。这个函数通常接受一个日期或日期时间作为参数，并返回一个表示月份的整数值。

2. **`= 8`**：这是一个比较操作，用于检查提取的月份是否等于8。

所以，`month(qpd.date) = 8` 的含义是：如果从日期 `qpd.date` 中提取的月份部分等于8，那么这个条件将评估为真，否则为假。

相关的知识点包括：

- **SQL日期函数**：SQL提供了一系列日期和时间函数，用于处理日期和时间数据。其中，`month()` 是一个常见的函数，用于提取日期中的月份部分。其他日期函数还包括 `day()`（提取天）、`year()`（提取年份）等。

- **比较操作符**：`= 8` 是一个比较操作符，用于比较两个值是否相等。在这种情况下，它比较提取的月份是否等于8。

- **条件表达式**：`month(qpd.date) = 8` 是一个条件表达式，通常用于筛选数据，以便仅包括满足特定条件的记录。

- **日期和时间处理**：在SQL中，日期和时间数据的处理非常常见，因为它们在许多应用中都起着关键作用。在查询中，您通常会使用日期函数和条件来对日期数据进行操作和筛选。

2）细节2：

`count()` 和 `sum()` 是 SQL 中用于聚合数据的函数，但它们用于不同的目的，因此有一些重要的区别：

1. **count() 函数**：
- `count()` 用于计算某个列中非 NULL 值的行数。
- 它通常用于计算特定列中的记录数。例如，`count(question_id)` 计算 'question_id' 列中非 NULL 值的行数，因此它表示问题的总数量。
- 结果总是一个整数值，表示计数。

2. **sum() 函数**：
- `sum()` 用于计算某个列中数值的总和。
- 它通常用于计算数值列的总和。在这种情况下，`sum(if(qpd.result='right', 1, 0))` 计算 'question_practice_detail' 表中 'result' 列值等于'right'的行的数量，因为它将满足条件的行（1）加在一起，得到总数。
- 结果是一个数值，可能是整数或小数，取决于列中的数据类型。

总结区别：
- `count()` 用于计算行数或特定列中非 NULL 值的行数，返回整数。
- `sum()` 用于计算数值列中的总和，返回数值。通常，它还可以用于计算特定条件下的行数，通过将条件映射为数值并对其进行求和。

3）细节3：

这两种写法的区别在于放置连接条件的位置以及其对结果集的影响。比较并解释这两种写法的区别与联系：

**代码1**：

```sql
select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id 
where up.university = '复旦大学' and month(qpd.date) = 8
group by up.device_id
```

**代码2**：

```sql
select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id  and month(qpd.date) = 8
where up.university = '复旦大学'
group by up.device_id
```

**区别**：

- **代码1** 将 `month(qpd.date) = 8` 放在 `WHERE` 子句中，这意味着在连接 `user_profile` 和 `question_practice_detail` 表之前，先筛选出月份为8的记录。这将导致只有符合月份条件的记录将被关联，且可能导致某些 `user_profile` 表中的用户信息在结果中缺失。
- **代码2** 将 `month(qpd.date) = 8` 放在 `ON` 子句中，这意味着在连接 `user_profile` 和 `question_practice_detail` 表时，已经包括了月份条件。这将确保所有 `user_profile` 表中的用户信息都包括在结果中，即使他们在月份为8之外没有问题练习记录。

**联系**：

- 共同之处是两个查询的结果集都包括了属于'复旦大学'的用户，并且都计算了问题总数（question_cnt）和正确问题总数（right_question_cnt）。
- 两种写法都使用了 `LEFT JOIN` 来关联 `user_profile` 和 `question_practice_detail` 表，以保留 `user_profile` 表中所有用户的信息。

**选择哪种写法** 取决于您的需求。如果您希望仅包括在特定月份内有问题练习记录的用户信息，那么使用代码1的方式是合适的。如果您希望包括所有用户信息，并且只筛选问题练习记录的月份为8，那么使用代码2的方式是更合适的。

二、完整代码

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id  and month(qpd.date) = 8
where up.university = '复旦大学'
group by up.device_id