选择两个具有不同条件的表

The table1 has only index column and the table2 is just to contain versions.

table1只有索引列,table2只包含版本。

I want to select every index and its latest version. (the yellow cells)

我想选择每个索引及其最新版本。 (黄色细胞)

The table2 has datetime column to know which one is latest.

table2有datetime列,可以知道哪一个是最新的。

When table1.idx = table2.belongTo, how to combine tables with a single query?

当table1.idx = table2.belongTo时,如何将表与单个查询组合?

1 个解决方案

#1

Edit; Corrected the answer to return all columns from table2.

编辑;更正了从table2返回所有列的答案。

You first have to derive the second table by selecting max(datetime) and then join based on the condition you gave.

首先必须通过选择max(datetime)来派生第二个表,然后根据您给出的条件进行连接。

Here the second table t2 will create row_number based on max(datetime) value for each BelongTo. Thanks to this answer for it.

这里,第二个表t2将基于每个BelongTo的max(datetime)值创建row_number。感谢这个答案。

select t1.*,t2.idx,t2.belongTo,t2.datetime1
from table1 t1 
inner join 
(
    select t11.idx,t11.belongTo,t11.datetime1,count(*) as row_number from 
    table2 t11
    inner join table2 t12
    on t11.belongTo=t12.belongTo
    and t11.datetime1 <= t12.datetime1
    group by t11.belongTo,t11.datetime1
) t2 /*this table will create row_number based on max datetime value for each belongTo*/
on t1.idx=t2.belongTo
where t2.row_number=1

See SQL Fiddle demo here

请参阅此处的SQL Fiddle演示

http://sqlfiddle.com/#!9/e5ada/10

#1