查找重复的concat值 [英] Find duplicate concat values

问题描述

  1 alex smith 
 2我试图在MySQL数据库中找到每一行都有重复的内容。 bob smith 
 3 alex smith 
  

我想返回：

  1 alex smith 
 3 alex smith 
  

此代码将查找重复项，但不会列出每个重复的行。

  SELECT 
 *，
 CONCAT（`firstName`，''，`lastName`）为全名，
 COUNT（*）d 
 FROM用户
 GROUP BY全名
 HAVING d> 1; 
  

下面的代码是我试图让每一行都是重复的，但是我得到IN / ALL / ANY子查询中的＃1054 - 未知列'full_name'

  SELECT CONCAT（`firstName全名
 FROM用户
 WHERE full_name IN（
 SELECT CONCAT（`firstName`，''，`lastName`）为全名
 FROM users 
 GROUP BY full_name 
 HAVING COUNT（full_name）> 1 
）
  

解决方案

你不能在WHERE子句中使用别名;并且你实际上并不需要CONCAT （在这种情况下它会增加你的计算成本）;您可以比较元组。

pre code $ SELECT
FROM用户
WHERE（`firstName`， `lastName`）IN（
SELECT`firstName`，`lastName`
FROM users
GROUP BY`firstName`，`lastName`
HAVING COUNT（*）> 1
）;

I'm trying to find each row with duplicates in a MySQL database.

1   alex    smith
2   bob     smith
3   alex    smith

I want to return:

1   alex    smith
3   alex    smith

This code will find duplicates, but it doesn't list each row that is a duplicate.

SELECT
    *,
    CONCAT(`firstName`, ' ', `lastName`) as full_name,
    COUNT(*) d
FROM users
GROUP BY full_name
HAVING d > 1;  

The code below is what I'm trying to get each row that is a dupliate, but I get the error "#1054 - Unknown column 'full_name' in 'IN/ALL/ANY subquery'"

SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name
FROM users
WHERE full_name IN (
    SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name
    FROM users
    GROUP BY full_name
    HAVING COUNT(full_name) > 1
)

解决方案

You cannot use aliases in WHERE clauses; and you do not actually need the CONCAT (it increases your computational costs in this case); instead you can compare "tuples".

SELECT *
FROM users
WHERE (`firstName`, `lastName`) IN (
    SELECT `firstName`, `lastName`
    FROM users
    GROUP BY `firstName`, `lastName`
    HAVING COUNT(*) > 1
);

这篇关于查找重复的concat值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！