SQL Server - Check duplicate data in two simultaneous fields

Question

DBMS: SQL Server 2014;

Problem: I need to know which records are duplicated, so that it is checked in two simultaneous fields, that is, if the field "CPF" with the value '83971465842' and the field "MAT" with the value '001' exists, identically, in another tuple. As shown in the example below:

CPF           MAT
83971465842   001
83971465842   001
57650942287   111
57655707200   305
57656959220   916
57656959220   209

Note that in the first two records, the values '83971465842' + '001' are in two different lines. These are precisely what I need to identify. How to do the consultation?

Answer 1

select CPF, MAT, count(*)
from Tabela
group by CPF, MAT
having count(*) > 1

Answer 2

Select *
From tabela t1
Where       exists (select CPF, MAT
                    from Tabela t2
                    where t2.cpf = t1.cpf
                    and   t2.mat = t1.mat
                    group by CPF, MAT 
                    having count(*) > 1)

Expanding a little the answer above listing all the records of the duplicate table, the next step , I believe, would be some action in duplicates, delete or change, this query is the basis for this.

I revisited the query because the thought syntax did not work in Sql Server

Answer 3

As a matter of fact, I did a select where in my table I have different names with equal CPF.

EX: ARTUR LIRA CPF: 024.309.258.23

ARTUR LIRA FILHO CPF: 024.309.258.23

SELECT array_to_string(array_agg(Trim(name)::varchar), '' >> ') AS names, Translate(Cpf,'./- ()',''''''''') AS Cpf, Count () AS total FROM table_clients GROUP BY Translate(Cpf,''./- ()',''''') HAVING COUNT() > 1 ORDER BY array_to_string(array_agg(Trim(name)::varchar), '' >>> '');

I have the following result.

total Cpf names ARTUR LIRA >>> ARTUR LIRA FILHO 024.309.258.23 2

I do not know it would be pertinent in this case but can solve the problem and remedy many doubts, my database is Postgresql.