表数据结构如下：
cardId price name
-------------------------------------------
001 0.9 aaa
002 1.5 bbb
001 0.5 ccc
003 1.2 ddd
SQL语句怎样去除重复（cardId）并且保留price较小的一条数据？
------解决方案--------------------
- SQL code
select * from tb t where price=(select min(price) from tb where cardid=t.cardid)
------解决方案--------------------
- SQL code
DELETE aFROM 你的表名 aWHERE EXISTS ( SELECT TOP 1 * FROM 你的表名 WHERE cardId = a.cardId AND price > a.price )
------解决方案--------------------
- SQL code
create table dashanliu(cardId char(5), price decimal(3,1), name char(5))insert into dashanliuselect '001', 0.9, 'aaa' union allselect '002', 1.5, 'bbb' union allselect '001', 0.5, 'ccc' union allselect '003', 1.2, 'ddd'select cardId,price,namefrom(select row_number() over(partition by cardId order by price) rn,cardId,price,name from dashanliu) twhere t.rn=1cardId price name------ --------------------------------------- -----001 0.5 ccc 002 1.5 bbb 003 1.2 ddd