时间戳

首页 > 解决方案 > 匹配两个 MySQL 表中的一个键并返回一个具有最大值的键

mysql - 匹配两个 MySQL 表中的一个键并返回一个具有最大值的键

问题描述

我在 MySQL 中有两个表：

表 1 称为Alpha

+-------------------------+---------------+---------------+-----------------+---------------+
| mID                     | mtype         | source        | state           | tstamp        |
+-------------------------+---------------+---------------+-----------------+---------------+
| 1111A-1111A-1111A-1111A | ON            | 12345         | NORMAL          | 1590000000000 |
| 1111A-1111A-3333A-3333A | ON            | 12345         | NORMAL          | 1590000020000 |
| 1111A-1111A-4444A-4444A | ON            | 12345         | NORMAL          | 1590000031000 |
| 1111A-1111A-5555A-5555A | ON            | 23456         | NORMAL          | 1590000040000 |
| 1111A-1111A-5555A-5555A | ON            | 23456         | NORMAL          | 1590000045000 |
| 1111A-1111A-6666A-6666A | ON            | 23456         | NORMAL          | 1590000050000 |
| 1111A-1111A-6666A-6666A | ON            | 23456         | NORMAL          | 1590000055000 |
| 1111A-1111A-7777A-7777A | ON            | 23456         | NORMAL          | 1590000061000 |
+-------------------------+---------------+---------------+-----------------+---------------+

表 2 称为Bravo

+-------------------------+-----------------+---------------+
| mID                     | mtype           | tstamp        |
+-------------------------+-----------------+---------------+
| 1111A-1111A-2222A-2222A | OFF             | 1590000010000 |
| 1111A-1111A-3333A-3333A | OFF             | 1590000021000 |
| 1111A-1111A-4444A-4444A | OFF             | 1590000030000 |
| 1111A-1111A-5555A-5555A | OFF             | 1590000041000 |
| 1111A-1111A-6666A-6666A | OFF             | 1590000051000 |
| 1111A-1111A-6666A-6666A | OFF             | 1590000056000 |
| 1111A-1111A-7777A-7777A | OFF             | 1590000060000 |
| 1111A-1111A-7777A-7777A | OFF             | 1590000065000 |
+-------------------------+-----------------+---------------+

我想编写一个 MYSQL 查询以通过以下方式连接这两个表：

将表 (Alpha) 与表 (Bravo) 匹配，如果：

所以预期的结果是：

+-------------------------+-----------------+---------------+
| mID                     | mtype           | tstamp        |
+-------------------------+-----------------+---------------+
| 1111A-1111A-1111A-1111A | ON              | 1590000000000 |
| 1111A-1111A-2222A-2222A | OFF             | 1590000010000 |
| 1111A-1111A-3333A-3333A | OFF             | 1590000021000 |
| 1111A-1111A-4444A-4444A | ON              | 1590000031000 |
| 1111A-1111A-5555A-5555A | ON              | 1590000045000 |
| 1111A-1111A-6666A-6666A | OFF             | 1590000056000 |
| 1111A-1111A-7777A-7777A | OFF             | 1590000065000 |
+-------------------------+-----------------+---------------+

我对 mySQL 不是很好，但我只是设法进行了一些连接，但无法按预期工作！

标签： mysqljoin

解决方案

MySQL 没有 FULL JOIN，因此您必须模拟它（mID从两个表中收集所有值）：

WITH cte AS ( SELECT mID FROM Alpha
              UNION
              SELECT mID FROM Bravo )
SELECT mID, GREATEST(COALESCE(Alpha.tstamp, 0), COALESCE(Bravo.tstamp, 0)) AS tstamp
FROM cte 
LEFT JOIN Alpha USING (mID)
LEFT JOIN Bravo USING (mID)

需要 COALESCE，因为 GREATEST 不喜欢参数中的 NULL。

或者，您可以使用

CASE WHEN Alpha.tstamp IS NULL THEN Bravo.tstamp
     WHEN Bravo.tstamp IS NULL THEN Alpha.tstamp
     ELSE GREATEST(Alpha.tstamp, Bravo.tstamp)
     END AS tstamp

它也会这样做。

另一种解决方案：

WITH cte AS ( SELECT mID, tstamp FROM Alpha
              UNION ALL
              SELECT mID, tstamp FROM Bravo )
SELECT mID, MAX(tstamp) tstamp
FROM cte
GROUP BY mID;

即使mID未定义为 UNIQUE，这也将起作用。

唯一的问题是当我也添加另一列“mType”时，它有时会给我错误的值。例如，对于 3、6、7，它的值应该是 OFF，但它的值是 ON？– 萨菲克

WITH 
cte1 AS ( SELECT mID, tstamp, mType FROM Alpha
          UNION ALL
          SELECT mID, tstamp, mType FROM Bravo ),
cte2 AS ( SELECT mID, tstamp, mType, 
                 ROW_NUMBER() OVER (PARTITION BY mID ORDER BY tstamp DESC) rn
          FROM cte1 )
SELECT mID, tstamp, mType
FROM cte2 
WHERE rn = 1;

如果存在 2 条相同和不同的记录，则必须返回什么记录mID-tstamp由mType您自己决定。

推荐阅读