SQL 列去重復(fù)

jsonhan 2015-11-27

展開全文

SQL去除列中重復(fù)ID

1.select * from his.tbStocktakeInfo

where StocktakeInfoID in (select StocktakeInfoID from

his.tbStocktakeInfo group by StocktakeInfoID having count(StocktakeInfoID) > 1)

2. 刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷，只留有rowid最小的記錄delete from people

where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重復(fù)記錄（多個字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、刪除表中多余的重復(fù)記錄（多個字段），只留有rowid最小的記錄
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重復(fù)記錄（多個字段），不包含rowid最小的記錄
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

6、select InventoryID,GoodsName,BatchNo,Format,Cost,GoodsID from dbo.v_BatchGoodsInventory as a

where BatchNo = (select MIN(BatchNo)

from dbo.v_BatchGoodsInventory as b

where a.GoodsID = b.GoodsID )AND StoreID='C8E43A8D-0311-490F-AA5A-84260DFC87EA'

AND a.Status=1 AND a.GoodsStatus=1 ORDER BY a.GoodsName;