时间戳

首页 > 解决方案 > 在mysql中选择具有最大重复项的行以及不同的数据

mysql - 在mysql中选择具有最大重复项的行以及不同的数据

问题描述

我有一个场景，我需要从一个表中获取最后 3 小时的数据，方法是将它与另一个表连接起来。请在下面找到 2 个表格

1.采购表

| PURCHASE_ID | ORDER_NO | PRODUCT_TYPE | PURCHASE_DATE       | CREATE_DATE         | MODIFIED_DATE       |
|-------------|----------|--------------|---------------------|---------------------|---------------------|
|    500      | 612356   |  FOOD        | 2020-02-26 08:26:34 | 2020-02-26 08:26:34 | 2020-02-26 08:26:34 |
|    501      | 975432   |  FOOD        | 2020-02-26 08:29:12 | 2020-02-26 08:29:12 | 2020-02-26 08:29:12 |
|    502      | 156712   |  ELECTRONICS | 2020-02-26 08:32:18 | 2020-02-26 08:32:18 | 2020-02-26 08:32:18 |

1.PURCHASE_DETAIL 表

| PURCHASE_DETAIL_ID | PURCHASE_ID | MESSAGE_STATUS | CREATE_DATE         | MODIFIED_DATE      |   
|--------------------|-------------|----------------|---------------------|--------------------|
|    1011            |      500    |      FAIL      | 2020-02-26 08:27:14 | 2020-02-26 08:27:14|
|    1012            |      500    |      SUCCESS   | 2020-02-26 08:27:56 | 2020-02-26 08:27:56|
|    1013            |      501    |      FAIL      | 2020-02-26 08:29:14 | 2020-02-26 08:29:14|
|    1014            |      501    |      SUCCESS   | 2020-02-26 08:29:58 | 2020-02-26 08:29:58|
|    1015            |      501    |      SUCCESS   | 2020-02-26 08:30:02 | 2020-02-26 08:30:02|
|    1016            |      501    |      FAIL      | 2020-02-26 08:30:37 | 2020-02-26 08:30:37|
|    1017            |      501    |      SUCCESS   | 2020-02-26 08:31:22 | 2020-02-26 08:31:22|
|    1018            |      502    |      FAIL      | 2020-02-26 08:32:24 | 2020-02-26 08:32:24|
|    1019            |      502    |      SUCCESS   | 2020-02-26 08:32:49 | 2020-02-26 08:32:49|
|    1020            |      502    |      FAIL      | 2020-02-26 08:33:03 | 2020-02-26 08:33:03|

结果应该只有最新MESSAGE_STATUS的失败并且表很大，我只需要最近3小时的数据。

结果我想要以下数据（我需要ORDER_NO唯一的）

1.采购表

| PURCHASE_ID | ORDER_NO | PRODUCT_TYPE |
|-------------|----------|--------------|
|    502      | 156712   |  ELECTRONICS |

我想出的最好的查询是

SELECT PURCHASE_ID, ORDER_NO, PRODUCT_TYPE FROM PURCHASE WHERE PURCHASE_ID IN (
SELECT A.PURCHASE_ID FROM PURCHASE A INNER JOIN
(SELECT PURCHASE_ID, MAX(PURCHASE_DETAIL_ID) AS NEW_ID, MESSAGE_STATUS 
FROM PURCHASE_DETAIL WHERE CREATE_DATE > DATE_SUB(NOW(),INTERVAL 3 HOUR) GROUP BY PURCHASE_ID) B
ON A.PURCHASE_ID = B.PURCHASE_ID AND A.PURCHASE_DETAIL_ID = B.NEW_ID 
AND A.MESSAGE_STATUS = "FAIL"
)

但这并没有给我预期的结果。任何人都可以看看，让我知道我错过了什么。我仍然是 SQL 的新手。

标签： mysqlsqljoin

解决方案

您可以通过执行内部连接和 order by 子句来解决这个问题

SELECT PURCHASE_ID, ORDER_NO, PRODUCT_TYPE
FROM purchase p INNER JOIN purchase_detail pd  ON p.purchase_id = pd.purchase_id WHERE 
message_status='fail' AND pd.create_date BETWEEN date.now() AND date.now()-3 ORDER BY 
pd.create_date DESC LIMIT 1

这将在三个小时的窗口内获取所有记录并获取 MESSAGE_STATUS 为失败的最新记录

推荐阅读