php - Find duplicates in table by checking 4 columns

Question

Table * articles contains 6 7 columns.

id  |  Article    | Date        |  Document   |   In    |   Out   |   Status
1       7156       2020-10-26      test           0          14        1
2       7157       2020-10-27      test 2         0          15        1
3       7158       2020-10-28      test 3         0          14        1 
4       7156       2020-10-26      test           0          14        1

Is there a way I can find and list all duplicates for 4 columns (article, document, in).

Duplicate for the table above would be row id 4

4       7156       2020-10-26      test           0          14        1

I was trying the query below, but dont get good result.

 select s.id, t.* 
    from [articles ] s
    join (
        select Article, Date, Document, Out, count(*) as qty
        from [articles]
        group by Article, Date, Document, Out
        having count(*) > 1
    ) t on s.Article= t.Article and s.Date= t.Date and s.Document= t.Document and s.Out = t.out

Is this possible to find directly through query in MYSQL?

标签： phpsqlcountduplicatessubquery

Answer 1

你可以做：

select *
from articles
where (article, document, in) in (
  select article, document, in 
  from articles 
  group by article, document, in
  habing count(*) > 1
)

这将返回两个重复的行：

id  |  Article    | Date        |  Document   |   In    |   Out   |   Status
1       7156       2020-10-26      test           0          14        1
4       7156       2020-10-26      test           0          14        1

或者，您可以使用ROW_NUMBER()基于其id. 例如：

select *
from (
  select *,
    row_number() over(partition by article, document, in order by id) as rn
  from articles
) x
where rn > 1