SQL Server Duplicate records but except one field

rta7y2nd 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(109)

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.

sql-server

来源：https://stackoverflow.com/questions/77197853/duplicate-records-but-except-one-field

2条答案

按热度按时间

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
)

赞(0）回复(0）举报 2023-10-15

z4iuyo4d2#

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

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Duplicate records but except one field

2条答案

相关问题

热门标签

最新问答