mysql - 想要仅根据列值消除重复记录

Question

http://sqlfiddle.com/#!9/ea4d2/1

这是要查看的 SQLfiddle。 storepkid 16 重复 2 次。但我只想要一次。我试过 DISTINCT 但它消除了整个重复行，所以在这种情况下它不起作用。我需要在查询中进行哪些更改才能获得正确的结果？请帮忙。

注意:忘记在最初的问题中提到我也尝试过 GROUP BY 但它在记录集结果中提供了不正确的数据。我尝试通过以下方式使用 GROUP BY。

http://sqlfiddle.com/#!9/ea4d2/20

http://sqlfiddle.com/#!9/ea4d2/17

注意 2:@Gabriel Valdez Timbol，我想要这样的结果。应删除具有重复 storepkid 的行。

| Storepkid | selldate | 
+---------------+-----------------------+
|     19    |  August, 25 2015 10:00:00 | 
|     12    |  August, 24 2015 19:00:00 | 
|     16    |  August, 24 2015 16:00:00 | 
|     15    |  August, 23 2015 13:00:00 | 
|     17    |  August, 21 2015 10:00:00 | 

Answer 1

您可以使用max 函数。使用以下查询

SELECT p.storepkid, max(p.selldate) AS recentselldate 
FROM 
(SELECT storepkid, purchasedatetime AS selldate 
        FROM t_product_purchase 
 UNION ALL 
 SELECT storepkid, starttime AS selldate 
        FROM t_service_purchase 
 UNION ALL 
 SELECT storepkid, selldatetime AS selldatetime 
       FROM t_coupon_purchase ) p 
GROUP BY storepkid
order by max(p.selldate)
DESC LIMIT 0,5

输出:

| Storepkid |        selldate           | 
+---------------------------------------+
|     19    |  August, 25 2015 10:00:00 | 
|     12    |  August, 24 2015 19:00:00 | 
|     16    |  August, 24 2015 16:00:00 | 
|     15    |  August, 23 2015 13:00:00 | 
|     14    |  August, 21 2015 13:15:00 | 

检查 DEMO HERE