SQL 查询 - 选择“上次更新”记录在一组中，更好的数据库设计？

Question

假设我有一个包含 3 个表的 MySQL 数据库：

表 1：Persons，有 1 列 ID (int)
表 2：时事通讯，具有 1 列 ID (int)
表 3：订阅，其中包含列 Person_ID (int)、Newsletter_ID (int)、Subscribed (bool)、Updated (Datetime)

Subscriptions.Person_ID 指向个人，Subscription.Newsletter_ID 指向新闻通讯。因此，每个人可能同时订阅 0 份或更多杂志。表订阅还将存储每个人订阅每个新闻通讯的完整历史记录。如果特定的 Person_ID-Newsletter_ID 对在 Subscriptions 表中没有行，则相当于该对的订阅状态为“false”。

以下是示例数据集

Persons
ID
1
2
3

Newsletters
ID
1
2
3

Subscriptions
Person_ID  Newsletter_ID  Subscribed  Updated
2                1           true     2010-05-01
3                1           true     2010-05-01
3                2           true     2010-05-10
3                1           false    2010-05-15

因此，截至 2010 年 5 月 16 日，人员 1 没有订阅，人员 2 订阅了新闻通讯 1，人员 3 订阅了新闻通讯 2。人员 3 订阅了新闻通讯 1，持续时间为有一段时间，但不再是了。

我正在尝试做两种查询。

1. 显示截至查询时间每个人的活动订阅的查询（我们可以假设将来永远不会更新 - 因此，这意味着返回每个 Person_ID-Newsletter_ID 对的最新“更新”值的记录，如下所示只要 Subscribed 为 true（如果 Person_ID-Newsletter_ID 对的最新记录的 Subscribed 状态为 false，那么我不希望返回该记录））。

2. 返回特定新闻通讯的所有活跃订阅的查询 - 与 1. 中有关“已订阅”列中包含“false”的记录的限定条件相同。

我使用 SQL/数据库的频率不够高，无法判断此设计是否良好，或者所需的 SQL 查询在订阅表中有 1M 条记录的数据库上是否会很慢。

我在 Visual Studio 2010 中使用可视化查询生成器工具，但我什至无法让查询返回每个 Person_ID-Newsletter_ID 对的最新更新记录。

是否有可能提出不涉及使用子查询的 SQL 查询（大概是因为对于较大的数据集它们会变得太慢）？如果不是，那么拥有一个单独的 Subscriptions_History 表会是一个更好的设计，并且每次将 Person_ID-Newsletter-ID 对的订阅状态添加到 Subscriptions 时，该对的任何现有记录都会移至 Subscriptions_History（这样 Subscriptions表只包含任何 Person_ID-Newsletter_ID 对的最新状态更新）？

我在 Windows 上使用 .net，那么使用 Linq 执行此类查询是否会更容易（或相同或更困难）？实体框架？

编辑：如果我使用此查询，会发生以下情况：

SELECT     Person_ID, Newsletter_ID, Allocation, Updated, MAX(Updated) AS Expr1
FROM         subscriptions
GROUP BY Person_ID, Newsletter_ID

我从订阅表中获得混杂在一起的第 2 行和第 4 行（在下面结果集的第 2 行中）：

Person_ID Newsletter_ID Subscribed Updated     Expr1 
2         1             true       2010-05-01  2010-05-01 
3         1             true       2010-05-01  2010-05-15 
3         2             true       2010-05-10  2010-05-10

谢谢！

Answer 1

我最近遇到了一个有点类似的问题。

我不是 SQL 专家，因此我无法就什么是最佳设计提供太多建议。但在专业人士介入之前，也许这会有所帮助：

SELECT s.Person_ID, s.Newsletter_ID  
FROM (
 SELECT MAX(ID) AS mid
 FROM Subscriptions
 GROUP BY 
  Person_ID,Newsletter_ID
) q
JOIN Subscriptions s
ON q.mid = s.ID
WHERE s.Subscribed = 1

请注意，我已在您的订阅表中添加了一个 ID 列（我将在稍后解释原因）。

现在，让我们分解一下它是如何工作的（或者我认为它是如何工作的，无论如何；如果我错了，我很高兴得到纠正）。

首先，您检索给定人员/新闻通讯的所有记录。这就是子查询的作用（是的，我知道你说过你不想有子查询，但我不确定你是否可以在没有子查询的情况下做到这一点）。我按 person_id 和 newsletter_id 进行分组。这可以返回多行。请注意，我选择了 MAX(ID)。如果您使用自动增量 ID 并且可以安全地假设 ID 列中编号最高的行是该组的最新行（即，如果您不手动插入 ID），则此子查询将为您提供最后一个的 ID每个人/时事通讯的行。

因此，您可以将其与订阅表连接起来：连接条件是订阅行的 ID 必须与您从子查询中检索到的 MAX id 相匹配。在这里，您仅考虑每个新闻通讯/个人的最新记录。然后，您可以使用 WHERE 条件排除不活动的订阅。

如果要将结果限制为给定的新闻通讯（或给定的人），请将该条件添加到 WHERE 子句中。

索引应该有助于使该查询运行得更快。

希望这有帮助。

已添加

如果由于某种原因您不能保证 MAX(Subscriptions.ID) 将对应于最后插入的行，您可能可以执行类似的操作（我认为遵循相同的逻辑，但是有点冗长，可能效率较低）：

SELECT Person_ID, Newsletter_ID  
FROM (
 SELECT MAX(Updated) AS upd, Newsletter_ID AS nid, Person_ID AS pid 
 FROM Subscriptions
 GROUP BY 
  Person_ID,Newsletter_ID
) q
JOIN Subscriptions s
ON q.pid = s.Person_ID AND q.nid = s.Newsletter_ID and q.upd = s.Updated
WHERE Subscribed = 1

新编辑

再想一想，我添加的替代方案（带有 MAX(Updated) 的方案）是错误的，我思考。您无法确定子查询中所选的 Newsletter_ID 和 Person_ID 将是与 MAX(Updated) 行对应的 Newsletter_ID 和 Person_ID。由于这些列用于连接条件，因此该查询可能会给出虚假结果。

Answer 2

将您的 Subscriptions 分成 2 个表：

• 首先将存储实际的订阅列表（目前为 true 的订阅）： Person_Id | Newsletter_Id
• 第二个将存储订阅日志（其更新或状态更改）

Answer 3

有序分析函数”是解决此类问题的标准方法。1M 条记录，没问题...当然，这取决于您机器的能力。MAX

(Updated) OVER(PARTITION BY 列表您想要“max”的字段 )

SELECT
  x.*
FROM
  (
    SELECT
       Person_ID
       , Newsletter_ID
       --, Subscribed
       , Updated
       , MAX(Updated) OVER( PARTITION BY Person_ID, Newsletter_ID, Subscribed) AS myUpdated
   FROM Subscriptions
  ) x 
WHERE Updated = myUpdated

Answer 4

我觉得你的设计很不错。子查询没有固有的缓慢性 - 如果是表达查询的最佳方式，请使用它们。

这是获取所有最新（即未覆盖）指令的查询：

SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated)
FROM Subscriptions GROUP BY Person_ID, Newsletter_ID

然后您可以使用此查询作为另一个查询的子查询来获取您想要的内容。对于查询 #1：

SELECT x.Person_ID, x.Newsletter_ID FROM
  (SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated) 
   FROM Subscriptions GROUP BY Person_ID, Newsletter_ID) x
WHERE x.Subscribed;

对于查询 #2：

SELECT x.Person_ID FROM
  (SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated)
   FROM Subscriptions GROUP BY Person_ID, Newsletter_ID) x
WHERE x.Subscribed AND x.Newsletter_ID = ?

您肯定需要在 Subscriptions 表中的 Newsletter_ID 上建立索引，因为此查询可能非常有选择性。

编辑：
糟糕，子查询中的 Subscriptions 列可以来自任意行，而不是生成 MAX(Updated) 的行。您必须重新加入原始表：

SELECT x.Person_ID, x.Newsletter_ID, y.Subscribed FROM
  (SELECT Person_ID, Newsletter_ID, MAX(Updated) as MaxUpdated
   From Subscriptions GROUP by Person_ID, Newsletter_ID) x
  JOIN Subscriptions y WHERE x.Person_ID = y.Person_ID AND
                             x.Newsletter_ID = y.Newsletter_ID AND
                             x.MaxUpdated = y.Updated