我想在MySQL数据库中取出重复的记录。这可以用:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

结果是:

100 MAIN ST    2

我想要拖动它,以便它显示复制的每一行。喜欢的东西:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

有什么想法可以做到吗?我试图避免做第一个,然后在代码中用第二个查询查找重复。


当前回答

SELECT * FROM booking WHERE DATE(created_at) = '2022-01-11' 和代码在( 从预订中选择代码 按代码分组 have COUNT(code) > )由id DESC订购

其他回答

    SELECT *
    FROM (SELECT  address, COUNT(id) AS cnt
    FROM list
    GROUP BY address
    HAVING ( COUNT(id) > 1 ))

另一个解决方案是使用表别名,如下所示:

SELECT p1.id, p2.id, p1.address
FROM list AS p1, list AS p2
WHERE p1.address = p2.address
AND p1.id != p2.id

在这种情况下,您真正要做的是获取原始的列表表,从中创建两个假装的表——p1和p2,然后在地址列上执行连接(第3行)。第4行确保相同的记录不会在结果集中多次出现(“重复重复”)。

关键是重写这个查询,以便它可以用作子查询。

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;

就我个人而言,这个问题解决了我的问题:

SELECT `SUB_ID`, COUNT(SRV_KW_ID) as subscriptions FROM `SUB_SUBSCR` group by SUB_ID, SRV_KW_ID HAVING subscriptions > 1;

这个脚本所做的是在表中显示所有存在过一次以上的订阅者ID,以及找到的重复的数量。

这是表的列:

| SUB_SUBSCR_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| MSI_ALIAS     | varchar(64) | YES  | UNI | NULL    |                |
| SUB_ID        | int(11)     | NO   | MUL | NULL    |                |    
| SRV_KW_ID     | int(11)     | NO   | MUL | NULL    |                |

希望对你也有帮助!

 SELECT firstname, lastname, address FROM list
 WHERE 
 Address in 
 (SELECT address FROM list
 GROUP BY address
 HAVING count(*) > 1)