Выборка из таблицы одинаковых записей

select
  FIO,
  KCH,
  NOM1
from table_you
where
  FIO in ('Алексеев','Коромыслов')

SELECT
  FIO,
  KCH,
  NOM1
FROM table_you
where FIO=(
    SELECT
      FIO
    FROM table_you
    group by
      FIO
    Having (FIO)>1 )

with s as
(select 'Алексеев'   fio, 1 kch, 2 nom1 union all
 select 'Мирошкин'   fio, 7 kch, 8 nom1 union all
 select 'Алексеев'   fio, 1 kch, 2 nom1 union all
 select 'Михайловы'  fio, 3 kch, 2 nom1 union all
 select 'Коромыслов' fio, 4 kch, 2 nom1 union all
 select 'Коромыслов' fio, 4 kch, 2 nom1
)
select fio, kch, nom1
from
 (select fio, kch, nom1,
  count(*) over (partition by fio, kch, nom1) cnt
  from s
 )s 
where cnt > 1;

select Запрос.* from
(select
  Поле1,
  Поле2,
  Поле3
  row_number() over (partition by Поле1, Поле2, Поле3 order by Поле1) as Кол,
  Поле4
from Table1) as Запрос
where Запрос.Кол > 1;

select
  Поле1, Поле2, Поле3, Поле4
from Table1
where (Поле1,Поле2,Поле3) in (select Поле1,Поле2,Поле3 from Table1 groyp by Поле1,Поле2,Поле3 having count(*) > 1).