求一个SQL 语句（关于删除）

[pennymay]

ID Length D1 D2
1 0 223 322
1 100 321 321
1 900 321 213
1 1000 132 233
2 20 223 322
2 322 223 322
2 1233 223 322
3 1 223 322
3 100 223 322
4 20 223 322
4 40 223 322
4 1000 223 322


每个ID只留一个length的最大值和最小值数据,其他不要,并把最小值都设为0

delete from a051DProdStemsDimensions 
where 
(
([length]<>(select min([length]) 
from a051DProdStemsDimensions as a051DProdStemsDimensions_1))
 and 
([length]<>(select max([length])
 from a051DProdStemsDimensions as a051DProdStemsDimensions_1))
)

这样写一共只留2条了,我希望每个ID都有2条,应该怎么写,谢谢

[中国风]

delete a
from a051DProdStemsDimensions a
where
[length]<>(select min([length]) from a051DProdStemsDimensions where ID=a.ID)
and
[length]<>(select max([length]) from a051DProdStemsDimensions where ID=a.ID)

[昵称被占用了]

delete A
from a051DProdStemsDimensions A
where 
(
([length]<>(select min([length]) 
from a051DProdStemsDimensions WHERE ID=A.ID))
 and 
([length]<>(select max([length])
 from a051DProdStemsDimensions WHERE ID=A.ID))
)

UPDATE A SET
   length=0
from a051DProdStemsDimensions A
WHERE [length]=(select min([length]) 
from a051DProdStemsDimensions WHERE ID=A.ID)

[wzy_love_sly]

delete t from a051DProdStemsDimensions t where not exists(
select 1 from a051DProdStemsDimensions where ID=t.id
group by id
having min([length])=t.[length] or max([length])=t.[length]
)

[wgzaaa]

delete from a051DProdStemsDimensions A
where exists(select 0 from a051DProdStemsDimensions B where A.id=b.id and length>a.length)
and exists(select 0 from a051DProdStemsDimensions B where A.id=b.id and length<a.length)

[中国风]

declare @T table([ID] int,[Length] int,[D1] int,[D2] int)
Insert @T
select 1,0,223,322 union all
select 1,100,321,321 union all
select 1,900,321,213 union all
select 1,1000,132,233 union all
select 2,20,223,322 union all
select 2,322,223,322 union all
select 2,1233,223,322 union all
select 3,1,223,322 union all
select 3,100,223,322 union all
select 4,20,223,322 union all
select 4,40,223,322 union all
select 4,1000,223,322

delete a
from 
	@T a
join
	 (select ID,min([length]) [minlength],max([length])[maxlength] from @T group by ID) b on a.[ID]=b.[ID] and a.[Length] not in(b.[minlength],[maxlength])


select * from @T


(12 個資料列受到影響)

(4 個資料列受到影響)
ID          Length      D1          D2
----------- ----------- ----------- -----------
1           0           223         322
1           1000        132         233
2           20          223         322
2           1233        223         322
3           1           223         322
3           100         223         322
4           20          223         322
4           1000        223         322

(8 個資料列受到影響)

[wzy_love_sly]

[code=SQL]create table tb(id int,length int,d1 int,d2 int)
insert into tb select 1,0,233,322
insert into tb select 1,100,321,321
insert into tb select 1,900,321,213
insert into tb select 1,1000,123,233
insert into tb select 2,20,223,322
insert into tb select 2,322,223,322
insert into tb select 2,1233,223,322
insert into tb select 3,1,223,322
insert into tb select 3,100,223,322
insert into tb select 4,20,223,322
insert into tb select 4,40,223,322
insert into tb select 4,1000,223,322

delete t from tb t where not exists(
select 1 from tb where ID=t.id
group by id
having min([length])=t.[length] or max([length])=t.[length]
)

id length d1 d2
1 0 233 322
1 1000 123 233
2 20 223 322
2 1233 223 322
3 1 223 322
3 100 223 322
4 20 223 322
4 1000 223 322

[wgzaaa]

create table a051DProdStemsDimensions(id int,length int,D1 int,D2 int)

insert a051DProdStemsDimensions select 1, 0 , 223 ,322
insert a051DProdStemsDimensions select 1, 100 , 321 ,321
insert a051DProdStemsDimensions select 1, 900 , 321 ,213
insert a051DProdStemsDimensions select 1, 1000 ,132 ,233
insert a051DProdStemsDimensions select 2, 20 ,223 ,322
insert a051DProdStemsDimensions select 2, 322 , 223, 322
insert a051DProdStemsDimensions select 2, 1233 ,223 ,322
insert a051DProdStemsDimensions select 3, 1 , 223, 322
insert a051DProdStemsDimensions select 3, 100 , 223, 322
insert a051DProdStemsDimensions select 4, 20 ,223 ,322
insert a051DProdStemsDimensions select 4, 40 ,223 ,322
insert a051DProdStemsDimensions select 4, 1000 ,223 ,322


delete a051DProdStemsDimensions from a051DProdStemsDimensions A
where exists(select 0 from a051DProdStemsDimensions B where A.id=b.id and length>a.length)
and exists(select 0 from a051DProdStemsDimensions B where A.id=b.id and length <a.length)
-------------------------------------------
select * from a051DProdStemsDimensions

[whw502]

create table #temp
(id int ,
length int,
d1 int,
d2 int)
insert into #temp
select 1  ,  0    ,  223 ,322 
union all
select 1    ,100  ,  321 ,321 
union all
select 1    ,900  ,  321 ,213 
union all
select 1    ,1000  ,  321 ,213 
union all
select 2    ,20  ,  321 ,321 
union all
select 2    ,322  ,  321 ,213 
union all
select 2    ,1233  ,  321 ,213 
union all
select 3    ,1  ,  321 ,213 
union all
select 3    ,100  ,  321 ,213 
union all
select 4    ,20  ,  321 ,213 
union all
select 4    ,40  ,  321 ,213 
union all
select 4    ,100  ,  321 ,213 

select * into temp from #temp 
delete from #temp 
insert into #temp 
select T1.id,T0.showlength,T1.d1,T1.d2   from 
(
select id,0 as showlength,min(length) as  length  from temp   group by id 
union all
select id,max(length) as showlength,max(length) as length from temp group by id 
) T0
join 
temp  T1
on T0.id=T1.id and T0.length = T1.length

select * from #temp
drop table #temp
drop table temp