在日期范围内获取最大数据而不重复MYSQL

Question

我很难提出我的问题。

SELECT * FROM YourTable AS T1
INNER JOIN
(SELECT user_id , MIN(some_timestamp)  AS some_timestamp FROM YourTable
GROUP BY user_id) AS T2
ON T1.User_Id = T2.User_Id AND T1.some_timestamp = T2.some_timestamp
WHERE Some_Timestamp BETWEEN '13-Jun-12 08:00' AND '13-Jun-12 10:00'

表名: YourTable

| PK_ID | USER_ID |  SOME_TIMESTAMP |
------------------------------------|
|     1 |     123 | 13-Jun-12 14:30 |
|     2 |     123 | 13-Jun-12 08:50 |
|     3 |     567 | 13-Jun-12 09:23 |
|     4 |     567 | 13-Jun-12 09:45 |
|     5 |     567 | 13-Jun-12 09:40 |
|     6 |     890 | 13-Jun-12 08:44 |   

我想得到的是这样

1. 从13-6月-12 08:00和13-6月-12 10:00之间的特定日期范围提取数据
2. 获取范围内的日期后，它将在不重复的情况下获取最新日期。

产出应如下：

1.日期范围

[13-Jun-12 08:00] & [13-Jun-12 10:00]

​

1. 只获取没有重复的最新时间戳。

​

结果应该是这样的

| PK_ID | USER_ID |  SOME_TIMESTAMP |
------------------------------------|
|     2 |     123 | 13-Jun-12 08:50 |
|     4 |     567 | 13-Jun-12 09:45 |
|     6 |     890 | 13-Jun-12 08:44 |

非常感谢大家！

Answer 1

SELECT 
    pk_id,
    user_id,
    MAX(some_timestamp)
FROM
    your_table
WHERE
    some_timestamp>= '13-Jun-12 08:00' 
AND some_timestamp<= '13-Jun-12 10:00'
GROUP BY
    user_id
ORDER BY
    pk_id ASC

SQL Fiddle

Answer 2

你的询问相当接近。需要选择MAX而不是MIN，并需要将WHERE条件移到子查询中：

SELECT * 
FROM YourTable AS T1
  INNER JOIN
  (SELECT user_id , MAX(some_timestamp)  AS some_timestamp 
   FROM YourTable
  WHERE Some_Timestamp BETWEEN '2013-06-12 08:00' AND '2013-06-12 10:00'
   GROUP BY user_id) AS T2
    ON T1.User_Id = T2.User_Id AND T1.some_timestamp = T2.some_timestamp

SQL Fiddle演示

返回：

PK_ID    USER_ID   SOME_TIMESTAMP
2        123       June, 12 2013 08:50
4        567       June, 12 2013 09:45
6        890       June, 12 2013 08:44

根据需要格式化您的SOME_TIMESTAMP字段。我还建议使用>=和<=，而不是BETWEEN，因为您可能无法在BETWEEN中获得您想要的结果。

Answer 3

我想，您需要在最大时间戳时的pk_id。

要在MySQL中做到这一点，通常必须将原始数据重新加入其中。但是，在您的例子中，pk_id似乎在增加时间戳。如果这是真的，那么下面的查询应该返回您想要的内容：

select max(pk_id) as pk_id, user_id, max(some_timestamp)
FROM your_table
WHERE Some_Timestamp between '13-Jun-12 08:00' AND Some_Timestamp <= '13-Jun-12 10:00'
GROUP BY user_id
ORDER BY pk_id ASC

如果不包括max(pk_id)，那么将返回任意的pk_id，它与最大时间戳无关。