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)
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)
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)
select 1 from a051DProdStemsDimensions where ID=t.id
group by id
having min([length])=t.[length] or max([length])=t.[length]
)
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)
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,322delete 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 個資料列受到影響)
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,322delete 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
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
(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.lengthselect * from #temp
drop table #temp
drop table temp