# Heading
# 按条件a分组之后取条件b最大的数据
SELECT
*
FROM
( SELECT * FROM tmp HAVING 1 ORDER BY modify_index DESC ) t
GROUP BY
index;
1
2
3
4
5
6
2
3
4
5
6
SELECT
*
FROM
tmp a
WHERE
NOT EXISTS ( SELECT 1 FROM tmp b WHERE b.modify_index > a.modify_index AND b.index = a.index );
1
2
3
4
5
6
2
3
4
5
6
# 关联表b，更新表a的数据
UPDATE tmp
SET tmp.column1 =
(SELECT column1
FROM tmp1
WHERE tmp1.column2 = tmp.column3)
WHERE EXISTS
(SELECT 1
FROM tmp1
WHERE tmp1.column2 = tmp.column3);
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9