多表联接每一侧都需要最大版本

本文介绍了多表联接每一侧都需要最大版本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

所以我有两个表T1和T2-最初只有一个表具有版本"字段，现在它们都具有，并且我需要两者之间的联接来解决这个问题.

So I have two tables T1 and T2 - originally only one had a "version" field, now they both do and I need the join between the two to account for that.

T1 { modifyTime dateTime id int version int ruleId int ..... } T2 { modifyTime DateTime id int version int ..... }

我的旧查询是:

SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.ruleid = t2.id JOIN (SELECT max(version) AS version,id FROM t1 GROUP BY id) b ON t1.id=b.id AND t1.version=b.version

现在我需要相同的查询，但也要考虑T2的最大版本.尝试添加另一个JOIN(如第三个)，但未返回任何内容.同样，T2中的条目可能为空，我的意思是T1.ruleId可能为空.

Now I need the same query but the max version on T2 to also be considered. Tried adding another JOIN like the third, but that returned nothing. Also the entry in T2 may be blank, I mean T1.ruleId may be null.

推荐答案

您没有说您正在使用哪个RDBMS.如果您的用户支持WITH子句，则可能使该语句读起来更简洁.

You don't say which RDBMS you're using. If yours supports the WITH clause, that might make the statement read a little cleaner.

无论如何，我认为您是在正确的轨道上. RuleId可能是NULL的事实使事情变得复杂，但是类似的东西(小提琴)应该可以工作:

In any case, I think you were on the right track. The fact that ruleId may be NULL complicates things, but something like this (Fiddle) should work:

SELECT t1.*, t2.* FROM (SELECT t1.* FROM t1 JOIN (SELECT id, MAX(version) AS "VERSION" FROM t1 GROUP BY id) MAX_T1 ON MAX_T1.id = t1.id AND MAX_T1.version = t1.version ) t1 LEFT JOIN (SELECT t2.* FROM t2 JOIN (SELECT t2.id, MAX(t2.version) AS "VERSION" FROM t2 GROUP BY t2.id) MAX_T2 ON MAX_T2.id = t2.id AND MAX_T2.version = t2.version ) t2 ON t2.id = t1.ruleId ;