27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb') is not null drop table tb
go
create table tb (item_no int,item_subno varchar(2),bb varchar(27))
insert into tb
select 1,'01','...........................' union all
select 1,'01','...........................' union all
select 1,'02','...........................' union all
select 2,'11','...........................' union all
select 2,'33','...........................' union all
select 2,'33','...........................' union all
select 3,'11','...........................'
select identity(int,1,1) id ,item_no,item_subno,bb into #tmp from tb
select item_no,item_subno,bb from #tmp tb where id in (select top 1 id from #tmp where tb.item_no=#tmp.item_no)
--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go
create table tb (item_no int,item_subno varchar(2),bb varchar(27))
insert into tb
select 1,'01','...........................' union all
select 1,'01','...........................' union all
select 1,'02','...........................' union all
select 2,'11','...........................' union all
select 2,'33','...........................' union all
select 2,'33','...........................' union all
select 3,'11','...........................' union all
select 3,'11',null
select *,id=identity(int,1,1) into # from tb
delete a
from # a,# b
where a.item_no=b.item_no and a.id>b.id
delete from tb
alter table # drop column id
insert tb select * from #
select * from tb
item_no item_subno bb
----------- ---------- ---------------------------
1 01 ...........................
2 11 ...........................
3 11 ...........................
(3 行受影响)
drop table #
select item_no ,min(item_subno ) from 表 group by item_no