本文介绍了在 MySQL 表列中选择非重复记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一张只有 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 表列中选择非重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 19:19
查看更多