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

问题描述

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

目前我使用这个查询(带有一个简单的子查询):

<上一页>选择t1.id,t1.title,t1.contenttext,t1.fk_idothertablet1.版本FROM mytable 作为 t1WHERE (version = (SELECT MAX(version) AS topversion来自我的表WHERE (fk_idothertable = t1.fk_idothertable)))

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

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

索引视图似乎不能包含:

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

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

非常感谢！

解决方案

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

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

那么这个查询就是

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

没有子查询，没有 MAX 聚合.您总是知道当前版本是什么.您无需选择 max(version) 即可插入新记录.

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

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.

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:

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

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

Thanks a lot!

解决方案

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

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.

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