mysql去重復(fù) 保留一條

mysql去重復(fù) 保留一條怎么操作呢？一起來看下吧：

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

SELECT
????*
FROM
????people
WHERE
????peopleId?IN?(
????????SELECT
????????????peopleId
????????FROM
????????????people
????????GROUP?BY
????????????peopleId
????????HAVING
????????????count(peopleId)?>?1
????)

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

DELETE
FROM
????people
WHERE
????peopleName?IN?(
????????SELECT
????????????peopleName
????????FROM
????????????people
????????GROUP?BY
????????????peopleName
????????HAVING
????????????count(peopleName)?>?1
????)
AND?peopleId?NOT?IN?(
????SELECT
????????min(peopleId)
????FROM
????????people
????GROUP?BY
????????peopleName
????HAVING
????????count(peopleName)?>?1
)

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

SELECT
????*
FROM
????vitae?a
WHERE
????(a.peopleId,?a.seq)?IN?(
????????SELECT
????????????peopleId,
????????????seq
????????FROM
????????????vitae
????????GROUP?BY
????????????peopleId,
????????????seq
????????HAVING
????????????count(*)?>?1
????)

4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有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ù)記錄（多個(gè)字段），不包含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、消除一個(gè)字段的左邊的第一位：

UPDATE?tableName
SET?[?Title?]=?RIGHT?([?Title?],(len([?Title?])?-?1))
WHERE
????Title?LIKE?'村%'

7、消除一個(gè)字段的右邊的第一位：

UPDATE?tableName
SET?[?Title?]=?LEFT?([?Title?],(len([?Title?])?-?1))
WHERE
????Title?LIKE?'%村'

8、假刪除表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄

UPDATE?vitae
SET?ispass?=-?1
WHERE
????peopleId?IN?(
????????SELECT
????????????peopleId
????????FROM
????????????vitae
????????GROUP?BY
????????????peopleId

以上就是小編今天的分享，希望可以幫助到大家。