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

How to convert a SQL subquery to a join

提问by sachaa

I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content".

我有两个具有 1:n 关系的表：“内容”和“版本化内容数据”（例如，文章实体和该文章创建的所有版本）。我想创建一个显示每个“内容”的顶级版本的视图。

Currently I use this query (with a simple subquery):

目前我使用这个查询（带有一个简单的子查询）：

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)))

The subquery is actually a query to the same table that extracts the highest version of a specific item. Notice that the versioned items will have the same fk_idothertable.

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

In SQL Server I tried to create an indexed viewof this query but it seems I'm not able since subqueries are not allowed in indexed views. So... here's my question... Can you think of a way to convert this query to some sort of query with JOINs?

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

It seems like indexed views cannot contain:

似乎索引视图不能包含：

• subqueries
• common table expressions
• derived tables
• HAVING clauses

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

I'm desperate. Any other ideas are welcome :-)

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

Thanks a lot!

非常感谢！

回答by jmucchiello

This probably won't help if table is already in production but the right way to model this is to make version = 0 the permanent version and always increment the version of OLDER material. So when you insert a new version you would say:

如果 table 已经在生产中，这可能无济于事，但建模的正确方法是使 version = 0 成为永久版本，并始终增加 OLDER 材料的版本。因此，当您插入新版本时，您会说：

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

Then this query would just be

那么这个查询就是

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

No subqueries, no MAX aggregation. You always know what the current version is. You never have to select max(version) in order to insert the new record.

没有子查询，没有 MAX 聚合。您总是知道当前版本是什么。您永远不必选择 max(version) 来插入新记录。

回答by jpalecek

Maybe something like this?

也许是这样的？

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)

Just a wild guess...

只是一个疯狂的猜测......

回答by yothenberg

I think FerranB was close but didn't quite have the grouping right:

我认为 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

米

回答by Thava

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).

回答by Chris Shaffer

I don't know how efficient this would be, but:

我不知道这会有多有效，但是：

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

回答by Charles Graham

You Might be able to make the MAX a table alias that does group by.

您可能能够使 MAX 成为分组依据的表别名。

It might look something like this:

它可能看起来像这样：

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

回答by James

Like this...I assume that the 'mytable' in the subquery was a different actual table...so I called it mytable2. If it was the same table then this will still work, but then I imagine that fk_idothertable will just be 'id'.

像这样......我假设子查询中的“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

Hope this helps

希望这可以帮助