使用每个表的最高 id 将两个表中的信息合并为一个表

Question

我有两个表。

表 A 有一个唯一的 keyid 列和一个标识个人的 uid 列。每个 uid 有多个行。

表B 与表 A 相同，但其所有其他列都不存在于表 A 中。

我将创建一个表 C，其中包含表 A 和表 B 的所有信息

我想要的是一个查询，它为每个 uid 从 Tabla A 获取最高密钥 id，从表 B 中获取最高密钥 ID 并插入到表 C 中。

如何编写这样的查询？

编辑：这是两个表格的一些示例以及我想要的

TABLE A
keyid | uid | name | likes
-----------------------------
1     15    John   nothing
2     15    John   something
3     32    Jane   or other
TABLE B
keyid | uid | fruit | 
-----------------------------
1     15    oranges
2     32    banana
3     32    apple

TABLE C (resulting query)
uid | name | likes     | fruit
-------------------------------
15   John   something   oranges
32   Jane   or other    apple  

Answer 1

MySQL 或其他支持 row_number() over() 的数据库的替代方案

CREATE TABLE TableA(
keyid  INTEGER  NOT NULL PRIMARY KEY
,uid    INTEGER  NOT NULL
,name   VARCHAR(20)  NOT NULL
,likes  VARCHAR(40)  NOT NULL
);

INSERT INTO TableA(keyid,uid,name,likes) 
VALUES 
(1,15,'John','nothing'),
(2,15,'John','something'),
(3,32,'Jane','or other');

CREATE TABLE TableB(
keyid  VARCHAR(30) NOT NULL PRIMARY KEY
,uid    INTEGER  NOT NULL
,fruit  VARCHAR(40)  NOT NULL
);

INSERT INTO TableB(keyid,uid,fruit)
VALUES 
(1,15,'oranges'),
(2,32,'banana'),
(3,32,'apple');

select
a.uid, a.name, a.likes, b.fruit
from (
select *, row_number() over(partition by uid order by keyid DESC) rn
from TableA
) a
inner join (
select *, row_number() over(partition by uid order by keyid DESC) rn
from TableB
) b on a.uid = b.uid and b.rn = 1
where a.rn = 1

UID | 名称 | 喜欢 | 水果  --: |:--- |:-------- |:------  15 |约翰 |某物 |橘子  32 |简 |或其他 |苹果

数据库<>小提琴在这里

Answer 2

您可以使用CREATE TABLE ... SELECT查询，从uid的 MAX 值为keyid的每个表中选择该uid的值：

CREATE TABLE TableC AS
SELECT a.uid, a.name, a.likes, b.fruit
FROM (SELECT * 
FROM TableA a
WHERE a.keyid = (SELECT MAX(keyid) FROM TableA a2 WHERE a2.uid = a.uid)) a
JOIN (SELECT * 
FROM TableB b
WHERE b.keyid = (SELECT MAX(keyid) FROM TableB b2 WHERE b2.uid = b.uid)) b ON b.uid = a.uid

输出(针对示例数据)：

uid     name    likes       fruit
15      John    something   oranges
32      Jane    or other    apple

在 dbfiddle 上演示

更新

若要向表添加自动增量 id 列，只需在查询的CREATE TABLE部分中将其指定为列：

CREATE TABLE TableC (keyid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AS
SELECT a.uid, a.name, a.likes, b.fruit
FROM (SELECT * 
FROM TableA a
WHERE a.keyid = (SELECT MAX(keyid) FROM TableA a2 WHERE a2.uid = a.uid)) a
JOIN (SELECT * 
FROM TableB b
WHERE b.keyid = (SELECT MAX(keyid) FROM TableB b2 WHERE b2.uid = b.uid)) b ON b.uid = a.uid

输出：

keyid   uid     name    likes       fruit
1       15      John    something   oranges
2       32      Jane    or other    apple