萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> 查詢實現刪除

查詢實現刪除

   if object_id('t0807') is not null

  drop table t0807

  go

  create table t0807(AutoID int identity(1,1) primary key,[id] int,username varchar(10))

  go

  insert into t0807

  select 1,'1' union all

  select 4,'1' union all

  select 4,'2' union all

  select 6,'2' union all

  select 2,'3' union all

  select 7,'3'

  go

  --主鍵AutoID

  delete from t0807

  where AutoID not in

  (

  --查找出最小值

  select AutoID from t0807 a

  where [id]<=

  (select min([id]) from t0807 b where a.username=b.username)

  )

  go

  select * from t0807

  (所影響的行數為 6 行)

  (所影響的行數為 3 行)

  AutoID id username

  ----------- ----------- ----------

  1 1 1

  3 4 2

  5 2 3

  (所影響的行數為 3 行)

copyright © 萬盛學電腦網 all rights reserved