查找具有重复/相似列值的行 MySQL
我想从下表中选择在 fname 列中具有相似值的所有行作为它们的顺序中的第一行.IOW 从此表中我想检索 id 为 2,5 和 7 的行(因为anna"在anna"和michaela"之后>"和michaal"在michael"之后).
I want to select from the following table all the rows which have similar values in the fname column as the first in their order. IOW from this table I want to retrieve rows with ids 2,5 and 7 (because " anna" comes after "anna", and "michaela" and "michaal" come after "michael").
+----+------------+----------+
| id | fname | lname |
+----+------------+----------+
| 1 | anna | milski |
| 2 | anna | nguyen |
| 3 | michael | michaels |
| 4 | james | bond |
| 5 | michaela | king |
| 6 | bruce | smart |
| 7 | michaal | hardy |
+----+------------+----------+
到目前为止，我所拥有的是:
What I have so far is this:
select *, count(fname) cnt
from users group by soundex(fname)
having count(soundex(fname)) > 1;
但由于我将其分组，因此结果是
but since I'm grouping it the result is
+----+----------+----------+-----+
| id | fname | lname | cnt |
+----+----------+----------+-----+
| 1 | anna | milski | 2 |
| 3 | michael | michaels | 3 |
+----+----------+----------+-----+
我要检索的是这样的:
+----+----------+----------+-----+
| id | fname | lname | cnt |
+----+----------+----------+-----+
| 2 | anna | nyugen | 2 |
| 5 | michaela | king | 3 |
| 7 | michaal | hardy | 3 |
+----+----------+----------+-----+
我应该对查询进行哪些更改?我尝试删除group by"，但它改变了结果(我可能错了，没有进行广泛的测试).
What should I change about the query? I tried removing "group by" but it changes the results (I could be wrong, haven't tested it extensively).
推荐答案
我重新阅读了您最初的问题，并提出了以下解决方案:
I've re-read your initial question and I've came up with the following solution:
SELECT *
FROM users
WHERE id IN
(SELECT id
FROM users t4
INNER JOIN
(SELECT soundex(fname) AS snd,
COUNT(*) AS cnt
FROM users AS t5
GROUP BY snd
HAVING cnt > 1
)
AS t6
ON soundex(t4.fname)=snd
)
AND id NOT IN
(SELECT MIN(t2.id) AS wanted
FROM users t2
INNER JOIN
(SELECT soundex(fname) AS snd,
COUNT(*) AS cnt
FROM users AS t1
GROUP BY snd
HAVING cnt > 1
)
AS t3
ON soundex(t2.fname)=snd
GROUP BY snd
);这有点过于复杂，但它可以工作并且完全符合您的要求:)
It's a bit over-complicated, but it works and delivers exactly what you asked for :)
相关文章