如何将SQL子查询转换为联接 [英] How to convert a SQL subquery to a join

问题描述

我有两个具有1:n关系的表:内容"和版本内容数据"(例如，商品实体和该商品创建的所有版本).我想创建一个显示每个内容"的最高版本的视图.

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

当前，我使用此查询(带有一个简单的子查询):

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

子查询实际上是对提取相同项目的最高版本的同一表的查询.请注意，版本化的项目将具有相同的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.

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

In SQL Server I tried to create an indexed view of 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?

似乎索引视图不能包含:

It seems like indexed views cannot contain:

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

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

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

非常感谢！

推荐答案

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

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:

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聚合.您总是知道当前版本是什么.您不必为了插入新记录而选择max(version).

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.

这篇关于如何将SQL子查询转换为联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！