banner
RustyNail

RustyNail

coder. 【blog】https://rustynail.me 【nostr】wss://ts.relays.world/ wss://relays.world/nostr
HomeArchives

mysql deduplication

35
AI Translation
This post is translated from Chinese into English through AI.View Original
AI-generated summary
This is a SQL code that selects the id and koto from a table called hitokoto, where koto is not duplicated and only the row with the smallest id is kept. It may be slow.

Direct code

SELECT
    id,
    koto
FROM
    hitokoto
WHERE
    koto IN (SELECT t1.koto FROM hitokoto t1 GROUP BY t1.koto HAVING COUNT(1) > 1)
    AND id NOT IN (SELECT MIN(id) FROM hitokoto t1 GROUP BY t1.koto HAVING COUNT(1) > 1)

Remove those with the same koto and keep the ones with the smallest id.

Retrieve all rows that have the same koto, find the smallest ones, and then use not in.

It seems a bit slow.

Loading...
Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.