新增字段IsCheckInvoice

Alter table T_LegalUnit add IsCheckInvoice bitUPDATE T_LegalUniT SET IsCheckInvoice=1 WHERE LegalUnitID='000010'SELECT *FROM T_LegalUnit WHERE IsCheckInvoice=1

1.查詢表中重復(fù)字段

SELECT MOBILE_PHONE FROM T_Saas_Device GROUP BY MOBILE_PHONE,CompanyGUID HAVING COUNT(*)>1

重復(fù)數(shù)據(jù),保留uid最小的數(shù)據(jù)

create database Test use Testif OBJECT_ID('users','U') is not null drop table users go create table users(uid int identity(1,1) primary key ,uname varchar(50) not null,uemail varchar(50) not null,sex char(2) not null ,createtime datetime not null )insert into users (uname,uemail,sex,createtime)values('張?bào)阌?#39;,'mi@163.com','女',getdate()); insert into users (uname,uemail,sex,createtime)values('王旭','wangxu@163.com','男',getdate()); insert into users (uname,uemail,sex,createtime)values('張曉','zhangxiao@163.com','女',getdate()); insert into users (uname,uemail,sex,createtime)values('劉小雨','xiaoyu@163.com','女',getdate()); insert into users (uname,uemail,sex,createtime)values('李青','liqing@163.com','男',getdate()); insert into users (uname,uemail,sex,createtime)values('肖珂','xiaoke@163.com','女',getdate()); insert into users (uname,uemail,sex,createtime)values('張西','zhangxi@163.com','男',getdate()); insert into users (uname,uemail,sex,createtime)values('程旭','chengxu@163.com','男',getdate());insert into users (uname,uemail,sex,createtime)values('小明','wang@163.com','男',getdate()); insert into users (uname,uemail,sex,createtime)values('小明','wang@163.com','男',getdate());select *from users

第一種寫法

delete u from users u where exists(select *from (select uname from users group by uname having count(uname)>1)as s where u.uname=s.uname) and u.uid not in (select min(uid) from users group by uname )

第二種寫法

delete from users where uid not in( select uid from users a where not exists ( select 1 from users b where a.uname = b.uname and b.uid < a.uid ))

查詢重復(fù)的用戶名

select *from users where uname in(select uname from users group by uname having count(*)>1)

查詢重復(fù)的用戶名次數(shù)和記錄

select count(*)as 重復(fù)次數(shù),uname from users group by uname having count(*)>1

查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（uname）來判斷

select *from users where uname in(select uname from users group by uname having count(uname)>1)

查找表中多余的重復(fù)記錄（多個(gè)字段）

select *from users u where exists (select *from (select uname,uemail from users group by uname,uemail having count(*)>1)as s where u.uname=s.uname and u.uemail=s.uemail)

查找表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄

select *from users u where exists(select *from (select uname,uemail from users group by uname,uemail having count(*)>1)as s where u.uname=s.uname and u.uemail=s.uemail) and u.uid not in(select min(uid)from users group by uname,uemail)

兩條相同的記錄 ，查詢只有uname相同的數(shù)據(jù)

select *from users where uname in(select uname from users group by uname having count(uname)>1)

兩條相同的記錄 ，查詢只有uname,uemail相同的數(shù)據(jù)

select *from users u where exists(select *from (select uname,uemail from users group by uname,uemail having count(*)>1 )as s where u.uname=s.uname and u.uemail=s.uemail)order by uname desc

刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（uname）來判斷，只留有uid最小的記錄

delete u from users u where exists (select *from (select uname from users group by uname having count(*)>1)as s where u.uname=s.uname ) and u.uid not in(select min(uid)from users group by uname )delete from users where uid not in( select uid from users a where not exists ( select 1 from users b where a.uname = b.uname and b.uid < a.uid ));

刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄

delete u from users u where exists(select *from (select uname,uemail from users group by uname,uemail having count(*)>1)as s where u.uname=s.uname and u.uemail=s.uemail)and u.uid not in(select min(uid)from users group by uname,uemail)

轉(zhuǎn)載于:https://www.cnblogs.com/imtudou/p/11251910.html

總結(jié)

以上是生活随笔為你收集整理的常用sql收藏的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。