如何删除不满足条件的记录

[tyouno1]

这里有一张表
商品编号 单价
1 1000
1 2000
1 3000
2 100
2 500
2 300

如何只保留同一个商品编号的最小单价的那条记录，将其他不满足条件的记录删除。
这个sql 怎么写？

[liangCK]

DELETE A
FROM tb AS A
    JOIN (
        SELECT 商品编号,MIN(单价) AS 单价
         FROM tb
        GROUP BY 商品编号
    ) AS B
        ON A.商品编号=B.商品编号
            AND A.单价>B.单价

[dtxh168]

create table [tb]([商品编号] int,[单价] int)
insert [tb]
select 1,1000 union all
select 1,2000 union all
select 1,3000 union all
select 2,100 union all
select 2,500 union all
select 2,300
 

delete t
from tb t
where 单价 not in(select min(单价) from tb where 商品编号=t.商品编号)

select * from [tb]
/**
商品编号        单价          
----------- ----------- 
1           1000
2           100
**/

[coconutyun]

首先创建这个表 a
create table a
(
sbbh char(2) not null
dj varchar(6) null
)

insert into a (column_1,column_2)
values(1,1000)
insert into a (column_1,column_2)
values(1,2000)
insert into a (column_1,column_2)
values(1,3000)
insert into a (column_1,column_2)
values(2,100)
insert into a (column_1,column_2)
values(2,500)
insert into a (column_1,column_2)
values(2,300)

[coconutyun]

接着

删除记录

delete t
from d t
where dj not in(select min(dj) from d where sbbh=t.sbbh)
最后显示结果记录

select *
from d

[coconutyun]

不好意思

删除表为 a 不是d

即应为：
delete t
from a t
where dj not in(select min(dj) from a where sbbh=t.sbbh)


select *
from a

[qqhmitzk]

delete from tb
where 单价 not in( select min(单价) from tb group by [商品编号] )

[tyouno1]

多谢了！！！！