SQL Server Duplicate records but except one field

rta7y2nd  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(108)

I have a table_1

ID  Seller  buyer   cost
100 abc     xyz     10.00
200 abc     xyz     10.00
300 abc     xyz     30.00

My goal to get select duplicate records and expect result

ID  Seller  buyer   cost
100 abc     xyz     10.00
200 abc     xyz     10.00

Query

select *
from Table_1 
where cost in (
    select cost
    from Table_1
    where seller = buyer
    group by cost
    having count(*) >= 2
)

This does not return any results.

of1yzvn4

of1yzvn41#

You can use EXISTS for this

select *
from Table_1 t1
where exists (
  select 1
  from Table_1 t2
  where t2.Seller = t1.Seller and t2.Buyer = t1.Buyer and t2.Cost = t1.Cost
  and t1.Id <> t2.Id
)
z4iuyo4d

z4iuyo4d2#

select *
from Table_2 
where cost in (
    select cost
    from Table_2
    group by cost
    having count(*) > 1
)

相关问题