在 MySQL 表列中选择非重复记录 [英] Select non-duplicate records in a MySQL table column

问题描述

我有一张只有 2 列的大桌子.一个是主 id 列，另一个是数据列.

I have a large table with just 2 column. One is the primary id column and other is a data column.

我只需要选择表中没有重复的记录.我尝试了下面的查询，但它需要很多时间并且不确定它是否真的有效.

I need to select only the records that is not-duplicated in the table. I tried the below query but it takes much time and not sure if it really work.

select * from (select column_name 
               from table_name 
               group by column_name 
               having count(*) = 1) x;

你怎么看?

如果它可以更快地完成工作，我也愿意接受其他尝试.

I am also open to other tries if it will do the job faster.

推荐答案

您可以左联接同一个表或使用子查询来检查重复项.这对于 SQL 服务器来说应该更容易，因为它不会计算所有重复项.像这样:

You can left join the same table or use subquery to check for duplicates. This should be easier for SQL server as it would not count all duplicates. Something like this:

SELECT
    t1.column_name 
FROM
    table_name AS t1
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            table_name AS t2
        WHERE
                t2.column_name = t1.column_name
            AND t2.id != t1.id
    )

或

SELECT
    t1.column_name 
FROM
    table_name AS t1
    LEFT JOIN table_name t2 ON (
        t2.column_name = t1.column_name
        t2.id != t1.id
    )
WHERE
    t2.column.name IS NULL

这篇关于在 MySQL 表列中选择非重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！