sql子查询示例
In the previous article we’ve practiced SQL, and today, we’ll continue with a few more SQL examples. The goal of this article is to start with a fairly simple query and move towards more complex queries. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. So, buckle up, we’re taking off!
在上一篇文章中，我们练习了SQL ，而今天，我们将继续其他一些SQL示例。 本文的目的是从一个非常简单的查询开始，然后转向更复杂的查询。 我们将在面试时检查您可能需要的查询，但在现实生活中还会查询一些查询。 所以，系好安全带，我们要起飞了！
资料模型 (Data Model)
As always, let’s first take a quick look at the data model we’ll use. This is the same model we’re using in this series, so you should be familiar by now. In case, you’re not, just take a quick look at the tables, and how are they related.
与往常一样，让我们首先快速浏览一下我们将使用的数据模型。 这与我们在本系列中使用的模型相同，因此您现在应该已经很熟悉了。 如果不是这样，只需快速浏览一下表格及其之间的关系即可。
We’ll analyze 6 SQL examples, starting from a pretty simple one. Each example will add something new, and we’ll discuss the learning goal behind each query. I’ll use the same approach covered in the article Learn SQL: How to Write a Complex SELECT Query? Let’s start.
我们将从一个非常简单的示例开始分析6个SQL示例。 每个示例都会添加一些新内容，我们将讨论每个查询背后的学习目标。 我将使用“ 学习SQL：如何编写复杂的SELECT查询？”一文中介绍的相同方法。 开始吧。
＃1 SQL示例– SELECT (#1 SQL Example – SELECT)
We want to examine what is in the call table in our model. Therefore, we need to select all attributes, and we’ll sort them first by employee_id and then by start_time.
我们想检查模型中调用表中的内容。 因此，我们需要选择所有属性，我们将首先按employee_id对它们进行排序，然后再按start_time对其进行排序。
-- A list of all calls (sorted by employee and start time)
SELECT *
FROM call
ORDER BY
call.employee_id ASC,
call.start_time ASC;
This is a pretty simple query and you should understand it without any problem. The only thing I would like to point here is that we’ve ordered our result first by the id of the employee (call.employee_id ASC) and then by the call start time (call.start_time). In real-life situations, this is something you would do if you want to perform analytics during the time on the given criteria (all data for the same employee are ordered one after another).
这是一个非常简单的查询，您应该毫无问题地理解它。 我想在这里指出的唯一一件事是，我们首先按雇员的ID（call.employee_id ASC）对结果进行排序，然后按呼叫开始时间（call.start_time）进行排序。 在现实生活中，如果要在给定条件下执行分析（如果同一员工的所有数据都被依次订购），则可以执行此操作。
＃2 SQL示例– DATEDIFF函数 (#2 SQL Example – DATEDIFF Function)
We need a query that shall return all call data, but also the duration of each call, in seconds. We’ll use the previous query as the starting point.
我们需要一个查询，该查询将返回所有呼叫数据，以及每个呼叫的持续时间（以秒为单位）。 我们将使用上一个查询作为起点。
-- A list of all calls together with the call duration
SELECT
call.*,
DATEDIFF("SECOND", call.start_time, call.end_time) AS call_duration
FROM call
ORDER BY
call.employee_id ASC,
call.start_time ASC;
The result returned is almost the same as in the previous query (same columns & order) except for one column added. We’ve named this column call_duration. To get the call duration, we’ve used the SQL Server DATEDIFF function. It takes 3 arguments, the unit for the difference (we need seconds), first date-time value (start time, lower value), second date-time value (end time, higher value). The function returns the time difference in the given unit.
返回的结果与上一个查询几乎相同（相同的列和顺序），只是添加了一个列。 我们已将此列命名为call_duration。 为了获得通话时间，我们使用了SQL Server DATEDIFF函数。 它需要3个参数，差的单位（我们需要秒），第一个日期时间值（开始时间，下限值），第二个日期时间值（结束时间，上限值）。 该函数以给定单位返回时差。
- Note: SQL Server has a number of (date & time) functions and we’ll cover the most important ones in upcoming articles.注意： SQL Server具有许多（日期和时间）功能，我们将在后续文章中介绍最重要的功能。
＃3 SQL示例– DATEDIFF +聚合函数 (#3 SQL Example – DATEDIFF + Aggregate Function)
Now we want to return the total duration of all calls for each employee. So, we want to have 1 row for each employee and the sum of the duration of all calls he ever made. We’ll continue from where we stopped with the previous query.
现在，我们要返回每个员工的所有通话的总时长。 因此，我们希望每个员工都有1行，以及他所进行的所有呼叫的持续时间之和。 我们将从上一个查询停止的地方继续。
-- SUM of call duration per each employee
SELECT
employee.id,
employee.first_name,
employee.last_name,
SUM(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_sum
FROM call
INNER JOIN employee ON call.employee_id = employee.id
GROUP BY
employee.id,
employee.first_name,
employee.last_name
ORDER BY
employee.id ASC;
There is nothing special to add regarding the result – we got exactly what we wanted. But let’s comment on how we achieved that. Few things I would like to emphasize here are:
关于结果，没有什么可添加的特殊内容–我们正是想要的。 但是，让我们评论一下我们是如何实现的。 我在这里要强调的几件事是：
- We’ve joined tables call and employee because we need data from both tables (employee details and call duration) 我们加入了表格呼叫和员工，因为我们需要两个表格中的数据（员工详细信息和通话时间）
- We’ve used the aggregate function SUM(…) around the previously calculated call duration for each employee 我们在先前计算的每位员工通话时长周围使用了聚合函数SUM（…）
- Since we’ve grouped everything on the employee level, we have exactly 1 row per employee 由于我们已将所有内容按员工级别分组，因此每个员工只有1行
- Note: There are no special rules when you combine the result returned by any function and aggregate function. In our case, you can use combine the SUM function with DATEDIFF without any problem.
- 注意：将任何函数和聚合函数返回的结果组合在一起时，没有特殊规则。 在我们的情况下，可以将SUM函数与DATEDIFF结合使用而没有任何问题。
＃4 SQL示例–计算比率 (#4 SQL Example – Calculating Ratio)
For each employee, we need to return all his calls with their duration. We also want to know the percentage of time an employee spent on this call, compared to the total call time of all his calls.
对于每位员工，我们需要返回其所有通话及其持续时间。 我们还想知道某个员工花费在此呼叫上的时间占其所有呼叫的总呼叫时间的百分比。
- Hint: We need to combine value calculated for one row with the aggregated value. To do that, we’ll use a subquery to calculate that aggregated value and then join into the related row.
- 提示：我们需要将为一行计算的值与合计值相结合。 为此，我们将使用子查询来计算该聚合值，然后加入相关行。
-- % of call duration per each employee compared to the duration of all his calls
SELECT
employee.id,
employee.first_name,
employee.last_name,
call.start_time,
call.end_time,
DATEDIFF("SECOND", call.start_time, call.end_time) AS call_duration,
duration_sum.call_duration_sum,
CAST( CAST(DATEDIFF("SECOND", call.start_time, call.end_time) AS DECIMAL(7,2)) / CAST(duration_sum.call_duration_sum AS DECIMAL(7,2)) AS DECIMAL(4,4)) AS call_percentage
FROM call
INNER JOIN employee ON call.employee_id = employee.id
INNER JOIN (
SELECT
employee.id,
SUM(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_sum
FROM call
INNER JOIN employee ON call.employee_id = employee.id
GROUP BY
employee.id
) AS duration_sum ON employee.id = duration_sum.id
ORDER BY
employee.id ASC,
call.start_time ASC;
You can notice that we’ve achieved in combining row values with aggregated value. This is very useful because you could put such calculations inside the SQL query and avoid additional work later. This query contains a few more interesting concepts that should be mentioned:
您可能会注意到，我们已经实现了将行值与聚合值相结合。 这非常有用，因为您可以将此类计算放入SQL查询中，并避免以后进行其他工作。 此查询包含一些更有趣的概念，应予以提及：
- The most important is that we’ve placed the entire query returning the aggregated value in the subquery (the part starting from the 2nd INNER JOIN (INNER JOIN () and ending with ) AS duration_sum ON employee.id = duration_sum.id. Between these brackets, we’ve placed the slightly modified query from part #2 SQL Example – DATEDIFF Function. This subquery returns the id of each employee and the SUM of all his calls durations. Just think of it as a single table with these two values
- 最重要的是，我们已将整个查询放置在子查询中（返回从第二个INNER JOIN（INNER JOIN（）到结尾）的部分），返回汇总值。AS duration_sum ON employee.id = duration_sum.id 。方括号中，我们放置了第2部分SQL示例– DATEDIFF函数中稍作修改的查询，该子查询返回每位员工的ID和其所有通话时间的总和。您可以将其视为包含这两个值的单个表
- We’ve joined the “table” from the previous bullet to tables call and employee because we need values from these two tables 我们将上一个项目符号中的“表格”加入到表格调用和员工中，因为我们需要这两个表格中的值
- We’ve already analyzed the DATEDIFF(…) function used to calculate the duration of a single call in part #2 SQL Example – DATEDIFF Function
- 我们已经在第2部分SQL示例– DATEDIFF函数中分析了用于计算单个调用持续时间的DATEDIFF（…）函数。
- This part CAST( CAST(DATEDIFF(“SECOND”, call.start_time, call.end_time) AS DECIMAL(7,2)) / CAST(duration_sum.call_duration_sum AS DECIMAL(7,2)) AS DECIMAL(4,4)) AS call_percentage is pretty important. First we’ve casted both dividend (CAST(DATEDIFF(“SECOND”, call.start_time, call.end_time) AS DECIMAL(7,2))) and divisor (CAST(duration_sum.call_duration_sum AS DECIMAL(7,2)) as decimal numbers. While they are whole numbers, the expected result is a decimal number, and we have to “tell” that to SQL Server. In case, we haven’t CAST-ed them, SQL Server would perform division of whole numbers. We’ve also cast the result as a decimal number. This wasn’t needed because we’ve previously defined that when casting dividend and divisor, but I wanted to format the result to have 4 numeric values, and all 4 of them will be decimal places (this is a percentage in decimal format)
- 这部分CAST（CAST（DATEDIFF（“ SECOND”，call.start_time，call.end_time）AS DECIMAL（7,2））/ CAST（duration_sum.call_duration_sum AS DECIMAL（7,2））AS DECIMAL（4,4）） AS call_percentage非常重要。 首先，我们将红利（ CAST（DATEDIFF（“ SECOND”，call.start_time，call.end_time）AS DECIMAL（7,2）） ）和除数（ CAST（duration_sum.call_duration_sum AS DECIMAL（7,2） ）都转换为十进制数，虽然它们是整数，但预期结果是十进制数，我们必须将其“告知” SQL Server，以防万一我们没有对它们进行CAST处理，SQL Server将对整数进行除法。我们也将结果转换为十进制数，这不是必需的，因为我们之前已经定义了在转换除数和除数时的定义，但是我想将结果格式化为具有4个数值，而所有4个数值都将是小数位（这是十进制格式的百分比）
From this example, we should remember that we can use subqueries to return additional values we need. Returning the aggregated value using a subquery and combining that value with the original row is one good example where we could do exactly that.
从这个例子中，我们应该记住，我们可以使用子查询来返回我们需要的其他值。 使用子查询返回汇总值并将该值与原始行合并是一个很好的例子，我们可以做到这一点。
＃5 SQL示例–平均值（AVG） (#5 SQL Example – Average (AVG))
We need two queries. First shall return the average call duration per employee, while the second shall return average call duration for all calls.
我们需要两个查询。 第一个应返回每个员工的平均通话时间，第二个应返回所有呼叫的平均通话时间。
-- average call duration per employee
SELECT
employee.id,
employee.first_name,
employee.last_name,
AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg
FROM call
INNER JOIN employee ON call.employee_id = employee.id
GROUP BY
employee.id,
employee.first_name,
employee.last_name
ORDER BY
employee.id ASC;
-- average call duration - all calls
SELECT
AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg
FROM call;
There is no need to explain this in more detail. Calculating the average call duration per employee is the same as calculating the SUM of call durations per employee (#3 SQL Example – DATEDIFF + Aggregate Function). We’ve just replaced the aggregate function SUM with AVG.
无需更详细地解释这一点。 计算每位员工的平均通话时间与计算每位员工的通话时间的总和相同（＃3 SQL示例– DATEDIFF +汇总函数）。 我们刚刚将汇总函数SUM替换为AVG。
The second query returns the AVG call duration of all calls. Notice that we haven’t used GROUP BY. We simply don’t need it, because all rows go into this group. This is one of the cases when aggregate function could be used without the GROUP BY clause.
第二个查询返回所有呼叫的AVG呼叫持续时间。 注意，我们还没有使用GROUP BY。 我们根本不需要它，因为所有行都属于该组。 这是不使用GROUP BY子句而可以使用聚合函数的情况之一。
＃6 SQL示例–比较AVG值 (#6 SQL Example – Compare AVG Values)
We need to calculate the difference between the average call duration for each employee and the average call duration for all calls.
我们需要计算每个员工的平均通话时间与所有通话的平均通话时间之差。
-- the difference between AVG call duration per employee and AVG call duration
SELECT
single_employee.id,
single_employee.first_name,
single_employee.last_name,
single_employee.call_duration_avg,
single_employee.call_duration_avg - avg_all.call_duration_avg AS avg_difference
FROM
(
SELECT
1 AS join_id,
employee.id,
employee.first_name,
employee.last_name,
AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg
FROM call
INNER JOIN employee ON call.employee_id = employee.id
GROUP BY
employee.id,
employee.first_name,
employee.last_name
) single_employee
INNER JOIN
(
SELECT
1 AS join_id,
AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg
FROM call
) avg_all ON avg_all.join_id = single_employee.join_id;
This query is really complex, so lets’ comment on the result first. We have exactly 1 row per employee with an average call duration per employee, and the difference between this average and average duration of all calls.
这个查询真的很复杂，因此让我们先对结果进行评论。 我们每位员工正好有1行，每位员工的平均通话时长，以及所有通话的平均通话时长与平均通话时长之间的差。
So, what we did to achieve this. Let’s mention the most important parts of this query:
因此，我们为实现这一目标所做的工作。 让我们提及此查询的最重要部分：
- We’ve again used a subquery to return the aggregated value – average duration of all calls 我们再次使用子查询返回汇总值-所有通话的平均时长
- Besides that, we’ve added this – 1 AS join_id. It serves the purpose to join these two queries using the id. We’ll “generate” the same value in the main subquery too 除此之外，我们添加了这个– 1个AS join_id。 它的目的是使用id将这两个查询联接在一起。 我们也将在主子查询中“生成”相同的值
- The “main” subquery returns data grouped on the employee level. Once more we’ve “generated” artificial key, we’ll use to join these two subqueries – 1 AS join_id “ main”子查询返回按员工级别分组的数据。 再一次，我们“生成”了人工密钥，我们将使用它来连接这两个子查询– 1 AS join_id
- We’ve joined subqueries using the artificial key (join_id) and calculated the difference between average values 我们使用人工键（join_id）加入了子查询，并计算了平均值之间的差
结论 (Conclusion)
I hope you’ve learned a lot in today’s article. The main thing I would like you to remember after this one is that you can perform many statistical computations directly in SQL, and then use the web form or Excel to present results using shiny tables and graphs. We’ll continue practicing in the next article, so stay tuned.
希望您在今天的文章中学到了很多东西。 在此之后，我想让您记住的主要事情是，您可以直接在SQL中执行许多统计计算，然后使用Web表单或Excel使用闪亮的表和图形来呈现结果。 我们将在下一篇文章中继续练习，请继续关注。
目录 (Table of contents)
sql子查询示例
2861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
