php SQL语句选择出现超过2次的重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14541307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Statement to select duplicate records appearing more than 2 times
提问by Khureem
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
我需要帮助来解决这种情况。我有一个包含记录的表，有一个字段 sku，在此记录中我多次出现 sku。表结构是这样的rid|id|sku|name
rid is auto_increment, where is id is varchar, if any sku is available on table multiple times the record looks like this
rid 是 auto_increment，其中 id 是 varchar，如果表上有多次可用的 sku，则记录看起来像这样
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 appears only once
我用这个sql语句来获取只出现一次的记录
SELECT *
FROM test
GROUP BY id
HAVING ( COUNT(id) = 1 )
This brings the records that are only added once, so according to above give record only rid 6 is the output
这带来了只添加一次的记录，所以根据上面给出的记录只有rid 6是输出
I tried to modify the above code to this to get the result of the records which are added 2 times
我尝试将上面的代码修改为此以获得添加2次的记录的结果
SELECT * FROM test
GROUP BY id
HAVING ( COUNT(id) = 2 )
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;
我得到的结果是那些添加了 2 次的记录，但问题是输出只出现 1 条这样的记录；
rid id sku name
--- -- ------ ------------
1 3 rs-123 test product
I need to fetch all rows of record that are added 2 times in the database. Please help
我需要获取在数据库中添加 2 次的所有记录行。请帮忙
回答by spencer7593
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
The inline view aliased as dreturns 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.
别名为 as 的内联视图d返回在表中出现多次的 sku 值。我们可以将该查询的结果连接到表中以获取具有匹配的 sku 的所有行。
Are idand skuinterchangeable? That wasn't clear to me. (If idis dependent on skuand skuis dependent on id, then you can replace references to skuwith references to idin that query.
是id和sku互换？我不清楚。（如果id依赖于sku并且sku依赖于id，那么您可以在该查询sku中用对的引用替换对的引用id。
回答by Jonathan Leffler
Test Driven Query Design — TDQD — to the fore.
测试驱动的查询设计 — TDQD — 脱颖而出。
Find the SKUs that appear more than once
查找出现多次的 SKU
SELECT sku
FROM test
GROUP BY sku
HAVING COUNT(*) > 1
Find the details for all the rows where the SKU appears more than once
查找 SKU 多次出现的所有行的详细信息
SELECT t.*
FROM test AS t
JOIN (SELECT sku
FROM test
GROUP BY sku
HAVING COUNT(*) > 1
) AS s
ON t.sku = s.sku
回答by Strawberry
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)
回答by Hamlet Hakobyan
When you group by Idyou can't get more than one Id in the group thus COUNT(Id)is always 1. Use this instead
当你分组时，Id你不能在组中获得多个 Id，因此COUNT(Id)总是 1。改用这个
SELECT * FROM test
GROUP BY id
HAVING ( COUNT(sku) = 2 )
If you want to get all records with more than one duplicate skuuse this:
如果您想获取具有多个重复项的所有记录，请sku使用以下命令：
SELECT * FROM test
GROUP BY id
HAVING ( COUNT(sku) > 1 )
回答by take
This query should work for you:
此查询应该适合您：
SELECT * FROM test WHERE
id IN(SELECT id FROM test group by id HAVING count(id) > 1)
