我有这张表:
+---+----+----+----+
|ID |KEY1|KEY2|COL1|
+---+----+----+----+
|001|aaa1|bbb1|ccc1|
|101|aaa1|bbb1|ddd2|
|002|aaa2|bbb2|eee3|
|102|aaa2|bbb2|fff4|
|003|aaa3|bbb3|ggg5|
|103|aaa3|bbb3|hhh6|
+---+----+----+----+
如果列
key1
和key2
相等,则结果必须包含具有最高ID的行。+---+----+----+----+
|ID |KEY1|KEY2|COL1|
+---+----+----+----+
|101|aaa1|bbb1|ddd2|
|102|aaa2|bbb2|fff4|
|103|aaa3|bbb3|hhh6|
+---+----+----+----+
在HQL中我不能像这样使用子查询:
select * from (select....)
如何执行此查询?
**解决方案**
实际上,解决方案要复杂一些,因为KEY1
和KEY2
位于另一个表中,该表使用两个键与第一个表连接。
+-----+-------+-------+-------+
|t1.ID|t2.KEY1|t2.KEY2|t1.COL1|
+-----+-------+-------+-------+
| 001| aaa1| bbb1| ccc1|
| 101| aaa1| bbb1| ddd2|
| 002| aaa2| bbb2| eee3|
| 102| aaa2| bbb2| fff4|
| 003| aaa3| bbb3| ggg5|
| 103| aaa3| bbb3| hhh6|
+-----+-------+-------+-------+
我使用了这个CORRECT
查询:
SELECT t1.ID, t2.KEY1, t2.KEY2, t1.COL1
FROM yourTable1 t1, yourTable2 t2
WHERE
t1.JoinCol1 = t2.JoinCol1 and t1.JoinCol2=t2.JoinCol2 and
t1.ID = (SELECT MAX(s1.ID) FROM yourTable1 s1, yourTable2 s2
WHERE
s1.JoinCol1 = s2.JoinCol1 and s1.JoinCol2=s2.JoinCol2 and
s2.KEY1 = t2.KEY1 AND s2.KEY2 = t2.KEY2)