如何从两个表中查找字段中的重复值以及它本身？

问题描述：

I have read a post with almost the same title, but it didn't answer my question. I have 2 table, table1 and table2. Both have col1 field

 SELECT
        *
    FROM
        table1
    GROUP BY
        col1
    HAVING 
        COUNT(col1) > 1

this code will give us data where col1 value is duplicated in table1, but I need to get value in col1 where the value is duplicated either in table1 OR table2, so there is 2 data having the same col1 value in table1, or same col1 value in table2 OR duplicated from 2 tables, so there is data in table1 where col1 = A, and there is data in table2 where col1 = A too.

答

Get the union of values from table1 and table2, and then group and filter that result. Use UNION ALL so that you do not lose duplicates when the union is performed.

SELECT * FROM (
    SELECT col1 FROM table1
    UNION ALL
    SELECT col1 from table2    -- you can change the column name as required
) AS blah
GROUP BY 1 HAVING count(*) > 1;

Note the reference to the column by field number in the GROUP BY. This allows you to use different column names in the different tables without having to use AS to alias the column name.

答

if i understood you want group 2 table data? for example if there is a col1='a' in table 1 and col1='a' in table 2 you want show that data? if yes , try this

select sel.col1 from (SELECT
        col1
    FROM
        table1
    GROUP BY
        col1
    union all
    SELECT
        col1
    FROM
        table2) sel
    GROUP BY
        sel.col1
    HAVING 
        COUNT(sel.col1) > 1

答

You may use below:

UNION ALL - if they have the same information schema
Join the tables then used HAVING clause

答

If I understand correctly, you don't care whether you find a value twice in table1 or twice in table2 or just once in table1 and once in table2; you consider all of these duplicates. So you want to look at the whole set built of all table1 records plus all table2 records. You get these with UNION ALL:

SELECT col1
FROM (SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2) t
GROUP BY col1
HAVING COUNT(*) > 1

如何从两个表中查找字段中的重复值以及它本身？

相关推荐