SELECT *
FROM t_info a
WHERE ((SELECT COUNT(*)
FROM t_info
WHERE Title = a.Title) > 1)
ORDER BY Title DESC
ä¸ãæ¥æ¾éå¤è®°å½
1ãæ¥æ¾å
¨é¨éå¤è®°å½
Select * From 表 Where éå¤å段 In (Select éå¤å段 From 表 Group By éå¤å段 Having Count(*)>1)
2ãè¿æ»¤éå¤è®°å½(åªæ¾ç¤ºä¸æ¡)
Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)
注ï¼æ¤å¤æ¾ç¤ºIDæ大ä¸æ¡è®°å½
äºãå é¤éå¤è®°å½
1ãå é¤å
¨é¨éå¤è®°å½ï¼æ
ç¨ï¼
Delete 表 Where éå¤å段 In (Select éå¤å段 From 表 Group By éå¤å段 Having Count(*)>1)
2ãä¿çä¸æ¡ï¼è¿ä¸ªåºè¯¥æ¯å¤§å¤æ°äººæéè¦ç ^_^ï¼
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
注ï¼æ¤å¤ä¿çIDæ大ä¸æ¡è®°å½
1ãæ¥æ¾è¡¨ä¸å¤ä½çéå¤è®°å½ï¼éå¤è®°å½æ¯æ ¹æ®å个å段ï¼peopleIdï¼æ¥å¤æ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2ãå é¤è¡¨ä¸å¤ä½çéå¤è®°å½ï¼éå¤è®°å½æ¯æ ¹æ®å个å段ï¼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ãæ¥æ¾è¡¨ä¸å¤ä½çéå¤è®°å½ï¼å¤ä¸ªå段ï¼
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4ãå é¤è¡¨ä¸å¤ä½çéå¤è®°å½ï¼å¤ä¸ªå段ï¼ï¼åªçæ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ãæ¥æ¾è¡¨ä¸å¤ä½çéå¤è®°å½ï¼å¤ä¸ªå段ï¼ï¼ä¸å
å«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)
è¡¥å
ï¼
æ两个以ä¸çéå¤è®°å½ï¼ä¸æ¯å®å
¨éå¤çè®°å½ï¼ä¹å³ææå段åéå¤çè®°å½ï¼äºæ¯é¨åå
³é®å段éå¤çè®°å½ï¼æ¯å¦Nameå段éå¤ï¼èå
¶ä»å段ä¸ä¸å®éå¤æé½éå¤å¯ä»¥å¿½ç¥ã
1ã对äºç¬¬ä¸ç§éå¤ï¼æ¯è¾å®¹æ解å³ï¼ä½¿ç¨
select distinct * from tableName
å°±å¯ä»¥å¾å°æ éå¤è®°å½çç»æéã
å¦æ该表éè¦å é¤éå¤çè®°å½ï¼éå¤è®°å½ä¿ç1æ¡ï¼ï¼å¯ä»¥æ以ä¸æ¹æ³å é¤
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
åçè¿ç§éå¤çåå æ¯è¡¨è®¾è®¡ä¸å¨äº§ççï¼å¢å å¯ä¸ç´¢å¼åå³å¯è§£å³ã
2ãè¿ç±»éå¤é®é¢é常è¦æ±ä¿çéå¤è®°å½ä¸ç第ä¸æ¡è®°å½ï¼æä½æ¹æ³å¦ä¸
å设æéå¤çå段为Name,Addressï¼è¦æ±å¾å°è¿ä¸¤ä¸ªå段å¯ä¸çç»æé
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
温馨提示:内容为网友见解,仅供参考