如何将SQL子查询转换为连接（join）

Question

我有两个具有1:n关系的表："content"和"versioned-content-data"（例如，一个文章实体及其创建的所有版本）。我想创建一个视图，显示每个“content”的顶部版本。

目前，我使用以下查询（带有简单的子查询）：

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1
WHERE (version = (SELECT MAX(version) AS topversion
                  FROM mytable
                  WHERE (fk_idothertable = t1.fk_idothertable)))

子查询实际上是对同一张表的查询，提取特定项的最高版本。请注意，版本化的项目将具有相同的fk_idothertable。

在SQL Server中，我尝试创建此查询的索引视图，但似乎我无法做到，因为不允许在索引视图中使用子查询。所以...这就是我的问题...你能想到将此查询转换为某种JOIN查询的方法吗？

看起来索引视图不能包含：

• 子查询
• 公共表达式
• 派生表
• HAVING子句

我很绝望。欢迎任何其他想法:-)

非常感谢！

- Sacha

---

你的子查询正确吗？我只看到一个表被引用。 - Mitch Wheat

是的，这是一个子查询，用于从同一张表中提取共享相同fk_idothertable的物品的最大版本。 - sachaa

顺便说一句... fk_idothertable 是指向主表的外键。 - sachaa

你能解释一下这个问题，以便我们可以看到实际的问题在哪里吗？ - jmucchiello

松散相关：https://dev59.com/iXRB5IYBdhLWcg3wEDul - Jon Smock

Answer 1

如果表格已经在生产中，这可能不会有所帮助，但正确的建模方式是将版本= 0作为永久版本，并始终增加旧材料的版本。因此，当您插入新版本时，应该这样说：

UPDATE thetable SET version = version + 1 WHERE id = :id
INSERT INTO thetable (id, version, title, ...) VALUES (:id, 0, :title, ...)

那么，这个查询将会是这样的：

SELECT id, title, ... FROM thetable WHERE version = 0

无子查询，无最大值聚合。您始终知道当前版本是什么。您无需选择 max(version) 以便插入新记录。

- jmucchiello

Answer 2

也许是这样的吗？

SELECT
  t2.id,
  t2.title,
  t2.contenttext,
  t2.fk_idothertable,
  t2.version
FROM mytable t1, mytable t2
WHERE t1.fk_idothertable == t2.fk_idothertable
GROUP BY t2.fk_idothertable, t2.version
HAVING t2.version=MAX(t1.version)

只是一个猜测...

- jpalecek

---

感谢您的回复。我真的很喜欢您解决方案的优雅性，但是当我执行它时，我遇到了以下问题：在选择列表中，列“mytable.id”无效，因为它既没有包含在聚合函数中，也没有包含在GROUP BY子句中。还有其他想法吗？ - sachaa

只需将SELECT中的所有字段添加到GROUP BY中 - 据我所知，标准需要这样做，但有些DBMS不需要。 - jpalecek

如果我这样做，每当标题在版本之间更改时，此项（具有相同的fk_idothertable）将在结果中出现两次（不同的标题+不同的版本）。 - sachaa

不，你所说的额外行将被HAVING子句删除。[我假设依赖关系（idother...，version）=> id => contenttext] - jpalecek

该死！在SQL Server中，索引视图也不能包含HAVING子句 :-( - sachaa

Answer 3

你可以将MAX作为表别名，并进行分组。

可能会像这样：

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1 JOIN
   (SELECT fk_idothertable, MAX(version) AS topversion
   FROM mytable
   GROUP BY fk_idothertable) as t2
ON t1.version = t2.topversion

- Charles Graham

Answer 4

我认为FerranB的想法很接近，但是分组还不够准确：

with
latest_versions as (
   select 
      max(version) as latest_version,
      fk_idothertable
   from 
      mytable
   group by 
      fk_idothertable
)
select
  t1.id, 
  t1.title, 
  t1.contenttext,
  t1.fk_idothertable,
  t1.version
from 
   mytable as t1
   join latest_versions on (t1.version = latest_versions.latest_version 
      and t1.fk_idothertable = latest_versions.fk_idothertable);

M

- Mark

---

谢谢Mark。这个查询确实返回了正确的结果，但由于它使用了“公共表达式”，所以对我来说无法在SQL Server中创建索引视图。 - sachaa

Answer 5

If SQL Server accepts LIMIT clause, I think the following should work:
SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1 ordery by t1.version DESC LIMIT 1;
(DESC - For descending sort; LIMIT 1 chooses only the first row and
DBMS usually does good optimization on seeing LIMIT).

- Thava

Answer 6

我不知道这个方法的效率如何，但是：

SELECT t1.*, t2.version
FROM mytable AS t1
    JOIN (
        SElECT mytable.fk_idothertable, MAX(mytable.version) AS version
        FROM mytable
    ) t2 ON t1.fk_idothertable = t2.fk_idothertable

- Chris

---

但是...即使使用GROUP BY，此查询基本上仍会返回来自mytable的所有列表项 + 每个项的最大版本。它不仅仅返回具有最高版本的项目。 - sachaa

Answer 7

就像这样...我假设子查询中的'mytable'是一个不同的实际表格...所以我称它为mytable2。如果它是相同的表格，那么这仍然可以工作，但我想fk_idothertable将只是'id'。

SELECT 
   t1.id, 
   t1.title, 
   t1.contenttext,
   t1.fk_idothertable
   t1.version
FROM mytable as t1
    INNER JOIN (SELECT MAX(Version) AS topversion,fk_idothertable FROM mytable2 GROUP BY fk_idothertable) t2
        ON t1.id = t2.fk_idothertable AND t1.version = t2.topversion

希望这能有所帮助

- James

---

子查询实际上是一个查询相同表格的操作，该操作提取特定项目的最高版本。请注意，具有相同版本的项目将具有相同的fk_idothertable。 - sachaa