连接与子查询SQL谜题

解决难题是学习SQL的好方法。在这个谜题中，我们将学习连接与子查询。

在许多查询中，您可以替换连接和子查询。然而，既然每个人都有自己的优势，那么这样做并不明智。一旦你解决了这个难题，你就会看到连接与子查询的一些优点和缺点。

解决难题是学习SQL的好方法。没有什么比练习你所学的更重要的了。一旦你弄清楚了这个谜题，请在评论中发布你的答案，以便我们大家互相学习。

SQL谜题——连接与子查询

系统管理员想要一份活跃用户的报告。您能否编写一份报告，返回自2017年3月14日以来30天内登录的任何用户的姓名、电话号码和最近日期？

（如果UserHistory中的action字段设置为“Logged On” ，您可以告诉用户已登录）。

User

• userID
• name
• phoneNumber

UserHistory

• userID
• actionDate
• action

每次用户登录时，都会在UserHistory表中插入一个新行，其中包含userID、当前日期和操作（其中action = “Logged On”）。

问题 #1 – 在不使用子查询的情况下编写SQL查询。

问题 #2 – 使用子查询编写SQL。

在回答问题时，请考虑联接与子查询的区别。哪种技术更适合解决问题？你能看到这两种情况的优势吗？

为了让您开始，请下载此脚本，其中包含表定义和一些测试数据。

对问题 #1 的回答——内部连接

像我写的大多数查询一样，我一步一步地完成了这个。让我通过引导您完成我的思考过程来向您展示我的意思。

不管这个查询结果如何，我知道我必须弄清楚如何在2017年3月14日之后的30天内获得登录信息。为此，我编写了一个简单的查询，该查询使用该DATEDIFF函数计算从actionDate到14日的天数。如果天数小于或等于30，我知道条目在窗口内。

SELECT *,
       DATEDIFF(DAY, actionDate, '2017-03-14')
FROM   @UserHistory
WHERE  action = 'Logged On'
       AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30;

DATEDIFF编码黑客

您会看到我在查询结果中包含了DATEDIFF的结果。我这样做是因为我总是把参数弄混！通过显示结果，我可以验证我的公式是否正确；因此，按预期在我的WHERE子句中使用。

这是我看到的：

现在我知道我可以在窗口内获得actionDate，我继续连接UserHistory与User，所以我可以在我的结果中包括PhoneNumber。我通过userID连接这些表。

SELECT U.Name,
       U.PhoneNumber,
       UH.actionDate AS RecentLogonDate
FROM   @User AS U
       INNER JOIN @UserHistory AS UH
       ON U.userID = UH.userID
WHERE  action = 'Logged On'
       AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30;

预期重复

鉴于每个用户有多个UserHistory条目，我希望看到重复项。我在下面强调了其中的一些：

剩下的最后一项任务是仅显示每个用户的最近登录日期。例如Bob，这将是2017-03-02。

请注意，对于每个用户，最近的日期是该用户的最大日期。这是一个很大的提示，因为它指向我们使用聚合函数，例如MAX。

因此，此时，我们可以使用GROUP BY来计算每个用户的最大日期。

SELECT   U.Name,
         U.PhoneNumber,
         MAX(UH.actionDate) AS RecentLogonDate
FROM     @User AS U
         INNER JOIN @UserHistory AS UH
         ON U.userID = UH.userID
WHERE    action = 'Logged On'
         AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30
GROUP BY U.Name, U.phoneNumber;

谁的结果是：

关键要点

有几个要点：

• 首先，通过查询获得中间结果并不可耻。使用公式时，例如DATEDIFF，请确保您得到您期望的计算。
• 其次，解决问题可能需要不止一种技术。在我们的例子中，我们首先连接项，一旦我们得到结果，就使用GROUP BY来获取最近的日期。
• 最后，保持简单！起初，我认为我必须再进行一次连接，以便比较日期，但我意识到这会变得复杂。另外，我想我可以对数据进行分区并使用窗口函数，但这似乎有点矫枉过正。一旦我意识到我只需要计算MAX，我意识到GROUP BY会起作用。

我相信还有其他方法可以解决这个难题。那么，你得到了什么答案？请在评论中分享。

问题 #2 的答案——子查询

让我们看看连接与子查询的第二个难题；子查询。

这个查询很优雅，因为该IN运算符自然地删除了重复项，但由于需要另一个子查询来获取最近的日期，所以变得很难看。

您会看到，当您需要根据另一个表中是否存在一个或多个条件而需要从一个表中返回行时，子查询真的很棒，但在将一个表中的数据与另一个表中的数据组合时并不那么迅速。

为了向您展示我的意思，请查看此查询，该查询为actionDate显示Windows内的用户和电话号码：

SELECT U.Name,
       U.PhoneNumber
FROM   @User AS U
WHERE  U.UserID IN (SELECT UserID
                    FROM   @UserHistory
                    WHERE  action = 'Logged On'
                    AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30);

通过子查询的自然唯一性

在这种情况下，当我们列出用户时，我们自然会得到一个唯一的姓名和电话号码列表。

表自然包含唯一值这一事实保证了唯一性（是的，我知道我没有在表上定义主键，但希望你看到它可能是一个）。

该子句中的子查询WHERE编译了一个userID列表，该列表的actionDate位于30天window内。

当然，我们的查询只写了一部分，因为它没有显示最新的actionDate。

如果我能有一点梦想（梦幻般的部分以粗体显示），我希望能够编写如下查询：

SELECT U.Name,
       U.PhoneNumber,
       (SELECT Max(UH.actionDate)
        FROM   @UserHistory AS UH
        WHERE  action = 'Logged On'
        AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30
AND    U.userID = UH.userID) AS RecentLogonDate
FROM   @User AS U
WHERE  DATEDIFF(DAY, RecentLogonDate, '2017-03-14') <= 30
       AND RecentLogonDate IS NOT NULL

但这不是一个正确编写的查询，SQL会发疯！

Msg 207, Level 16, State 1, Line 62
Invalid column name 'RecentLogonDate'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'RecentLogonDate'.

强制使用两个子查询！

使我们的子查询变得丑陋的原因是被迫使用两个子查询来检索最新的actionDate。为此，您必须实质上重复查询，但现在作为相关子查询。它只是User表UserID和UserHistory中的匹配。

SELECT U.Name,
       U.PhoneNumber,
       (SELECT Max(UH.actionDate)
        FROM   @UserHistory AS UH
        WHERE  action = 'Logged On'
               AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30
               AND U.userID = UH.userID) AS RecentLogonDate
FROM   @User AS U
WHERE  U.UserID IN (SELECT UserID
                    FROM   @UserHistory
                    WHERE  action = 'Logged On'
                    AND DATEDIFF(DAY, actionDate, '2017-03-14') <= 30);

结论

既然您已经看到了联接与子查询的区别，我希望您对何时使用一个与另一个有更好的理解。总而言之，联接擅长组合来自两个表的数据，子查询在测试一个表中是否存在另一个表中的值时最好。

您对连接与子查询有何经验？你喜欢什么时候用？请在评论中分享！

本文最初发布于Joins vs Subqueries SQL Puzzle - Essential SQL

https://www.codeproject.com/Articles/5326916/Joins-vs-Subqueries-SQL-Puzzle