SQL语句选择出现两次以上的重复记录 [英] SQL Statement to select duplicate records appearing more than 2 times

问题描述

我需要帮助以获取针对这种情况的解决方案.我有一个包含记录的表，有一个字段sku，在此记录中，我有sku出现多次. 表结构是这样的 rid | id | sku |名称

I need help to get the solution for this condition. I have a table containing records, there is a field sku, in this record i have sku's appearing multiple times. Table structure is like this rid|id|sku|name

rid是auto_increment，其中id是varchar，如果表上多次有任何sku可用，则记录看起来像这样

rid is auto_increment, where is id is varchar, if any sku is available on table multiple times the record looks like this

rid  id  sku     name
---  --  ------  --------------
1    3   rs-123  test product
2    3   rs-123  test product
3    4   rs-125  test product 2
4    4   rs-125  test product 2
5    4   rs-125  test product 2
6    6   rs-126  test product 3

我使用此sql语句获取仅显示一次的记录

I used this sql statement to get records that appears only once

SELECT *
FROM test
GROUP BY id
HAVING ( COUNT(id) = 1 )

这带来只添加一次的记录，因此根据上面的给出记录，仅输出6是输出

This brings the records that are only added once, so according to above give record only rid 6 is the output

我试图将上面的代码修改为此，以获得记录的结果，该记录被添加了2次

I tried to modify the above code to this to get the result of the records which are added 2 times

    SELECT * FROM test 
GROUP BY id 
HAVING ( COUNT(id) = 2 )

我得到的结果是添加了2次的那些记录，但是问题是输出只出现了1条这样的记录；

The result I am getting is of those record which are added 2 times, but the issue is the output is appearing only 1 record like this;

rid  id  sku     name
---  --  ------  ------------
1    3   rs-123  test product

我需要提取在数据库中添加了2次的所有记录行.请帮助

I need to fetch all rows of record that are added 2 times in the database. Please help

推荐答案

SELECT t.rid
     , t.id
     , t.sku
     , t.name
  FROM test t
  JOIN ( SELECT s.sku 
           FROM test s 
          GROUP BY s.sku
         HAVING COUNT(1) > 1
       ) d
    ON d.sku = t.sku

别名为d的嵌入式视图返回在表中多次出现的sku值.我们可以将该查询的结果连接到表中，以获取具有sku匹配的所有行.

The inline view aliased as d returns the sku values that appear more than once in the table. We can join the results of that query to the table to get all rows that have a sku that matches.

id和sku是否可以互换?我不清楚. (如果id依赖于sku，而sku依赖于id，则可以在该查询中将对sku的引用替换为对id的引用.

Are id and sku interchangeable? That wasn't clear to me. (If id is dependent on sku and sku is dependent on id, then you can replace references to sku with references to id in that query.

这篇关于SQL语句选择出现两次以上的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！