mysql - 使用mysql查询获取重复记录

Question

我试图在我的表中获取重复记录，但它无法完全正常工作，

我的表格如下所示:

id  vehicle     dates
---------------------------
1   GKB301J     2016-04-02
2   GKB301J     2016-04-02
3   GKB301J     2016-04-03
4   GKB275F     2016-04-14
5   GKB275F     2016-04-05
6   GKB275F     2016-04-14
7   GKB275F     2016-04-13
8   GKB438F     2016-04-29

从上表中，我期望得到:

id  vehicle     dates
 1  GKB301J     2016-04-02
 2  GKB301J     2016-04-02
 4  GKB275F     2016-04-14
 6  GKB275F     2016-04-14

但我的查询运行不佳:

下面是我的代码:

SELECT j.id, j.vehicle, j.dates
FROM ISSUANCE j
INNER JOIN (
    SELECT dates, vehicle
    FROM ISSUANCE
    GROUP BY DATE_FORMAT(dates, '%Y-%m-%d')
    HAVING COUNT(id) >= 2
) i ON DATE_FORMAT(j.dates, '%Y-%m-%d') = DATE_FORMAT(i.dates, '%Y-%m-%d') AND j.vehicle = i.vehicle
WHERE j.dates BETWEEN '2016-04-01' AND '2016-04-30'

Answer 1

您是否尝试过使用 SELECT DISTINCT - 应该返回唯一(非重复)记录。

查看: https://dev.mysql.com/doc/refman/5.7/en/select.html