Geek Answers Handbook

SQL Statement to select duplicate records that appear more than 2 times

I need help to get a solution for this condition. I have a table containing records, there is a sku field, in this record I have sku several times. Table structure similar to got rid | identifier | Code | name

rid - auto_increment, where id is varchar, if any sku is available in the table several times, the entry 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

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

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

This leads to the fact that records are added only once, therefore, according to the above description, only record 6 is the output

I tried changing the above code to this to get the result of entries that are added 2 times

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

The result that I get relates to the record that is added 2 times, but the problem is that only one record appears on the output, for example:

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

I need to get all rows of records that are added 2 times to the database. Please, help

+4
source share
5 answers
 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

An inline view with the alias d returns the sku values ​​that appear more than once in the table. We can join the results of this query to the table to get all rows with owls that match.

Are id and sku interchangeable? It was not clear to me. (If id depends on sku , and sku depends on id , you can replace links to sku links to id in this request.

+7
source

The development of a test query - TDQD - comes to the fore.

Find SKUs that appear more than once

 SELECT sku FROM test GROUP BY sku HAVING COUNT(*) > 1

Find details for all rows where SKU is displayed more than once

 SELECT t.* FROM test AS t JOIN (SELECT sku FROM test GROUP BY sku HAVING COUNT(*) > 1 ) AS s ON t.sku = s.sku
+4
source 
 CREATE TABLE test (rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,id INT NOT NULL ,sku VARCHAR(12) NOT NULL ,name VARCHAR(20) NOT NULL ); INSERT INTO test VALUES (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'); SELECT x.* FROM test x JOIN test y ON y.id = x.id GROUP BY x.rid HAVING COUNT(*) > 1; +-----+----+--------+----------------+ | 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 | +-----+----+--------+----------------+ 5 rows in set (0.01 sec)
+3
source

This query should work for you:

 SELECT * FROM test WHERE id IN(SELECT id FROM test group by id HAVING count(id) > 1)
0
source

When you group an Id , you cannot get more than one identifier in a group, so COUNT(Id) always 1. Use this instead

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

If you want all records with multiple sku duplicates to use this:

 SELECT * FROM test GROUP BY id HAVING ( COUNT(sku) > 1 )
0
source

More articles:


All Articles