表数据结构如下: cardId price name
-------------------------------------------
001 0.9 aaa
002 1.5 bbb
001 0.5 ccc
003 1.2 ddd
SQL语句怎样去除重复(cardId)并且保留price较小的一条数据?
-------------------------------------------
001 0.9 aaa
002 1.5 bbb
001 0.5 ccc
003 1.2 ddd
SQL语句怎样去除重复(cardId)并且保留price较小的一条数据?
from tb t
where not exists(selet 1 from tb
where cardid=t.cardid and price<t.price)
declare @T table (cardId varchar(3),price numeric(2,1),name varchar(3))
insert into @T
select '001',0.9,'aaa' union all
select '002',1.5,'bbb' union all
select '001',0.5,'ccc' union all
select '003',1.2,'ddd'DELETE a FROM @T a WHERE EXISTS (SELECT TOP 1 * FROM @T WHERE cardId=a.cardId AND price>a.price)select * from @T
/*
cardId price name
------ --------------------------------------- ----
001 0.9 aaa
002 1.5 bbb
003 1.2 ddd
*/
DELETE a
FROM 你的表名 a
WHERE EXISTS ( SELECT TOP 1
*
FROM 你的表名
WHERE cardId = a.cardId
AND price > a.price )
create table dashanliu
(cardId char(5), price decimal(3,1), name char(5))insert into dashanliu
select '001', 0.9, 'aaa' union all
select '002', 1.5, 'bbb' union all
select '001', 0.5, 'ccc' union all
select '003', 1.2, 'ddd'
select cardId,price,name
from
(select row_number() over(partition by cardId order by price) rn,
cardId,price,name from dashanliu) t
where t.rn=1cardId price name
------ --------------------------------------- -----
001 0.5 ccc
002 1.5 bbb
003 1.2 ddd