mysql - SQL 查询通过连接返回重复行

Question

类似的问题已被问过多次，但在这种情况下没有一个答案有帮助，所以再次提出问题

我有一个具有以下结构的表(是的，这里没有唯一的键):

---------------------------------------------------------------
|a_number | a_status | b_status | ref_id | timepublished       |
|(text)   | (text)   | (text)   |(text)  | (datetime)          |
---------------------------------------------------------------
|1        | U        | U        | re1    | 2016-08-12 13:24:25 |
|1        | P        | U        | re1    | 2016-08-12 13:24:35 |
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | U        | re2    | 2016-08-12 12:24:30 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

现在我要解决的问题是获取每个a_number的最新状态。所以正确的输出应该是:

---------------------------------------------------------------
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

我的查询是:

SELECT af.a_number
     , af.a_status
     , af.b_status
     , af.ref_id
     , af.timepublished
FROM af_biz af
 JOIN
 (SELECT MAX(timepublished) timepublished, ref_id
     FROM af_biz GROUP BY ref_id) tmp 
 ON tmp.timepublished = af.timepublished AND tmp.ref_id = af.ref_id
ORDER BY af.a_number;

但是我得到的结果包含这样的错误输出(请注意，时间是 max timepublished 但状态不同):

---------------------------------------------------------------
|1        | P        | U        | re1    | 2016-08-12 13:24:45 |
|1        | P        | P        | re1    | 2016-08-12 13:24:45 |
|2        | U        | U        | re2    | 2016-08-12 12:24:45 |
|2        | U        | F        | re1    | 2016-08-12 12:24:45 |
|4        | U        | U        | re3    | 2016-08-12 13:24:30 |
|4        | U        | U        | re4    | 2016-08-13 15:24:30 |
---------------------------------------------------------------

有人知道我的查询可能出了什么问题吗？

Answer 1

请尝试一下:

SELECT 
   af.a_number
 , af.a_status
 , af.b_status
 , af.ref_id
 , af.timepublished
FROM af_biz af
INNER JOIN 
(
    SELECT 
     a_number,
     MAX(timepublished) max_timepublished
    FROM af_biz 
    GROUP BY a_number
) AS t
ON af.a_number = t.a_number AND af.timepublished = t.max_timepublished
ORDER BY af.a_number

首先您需要按a_number分组。 ^{(因为您想要每个 a_number 的结果)}

第二之后需要加入匹配的timepublished和a_number ^{(不是ref_id )}