这篇博客讨论了如何使用SQL查询从表中选取fname列中有重复值的行，特别是那些值与首行相同的行。作者提供的初步查询返回了具有相同soundex编码的行，但未按要求筛选。解决方案是一个更复杂的查询，它结合了子查询来找出fname的重复项，并排除第一个出现的id，从而得到期望的结果。
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 |
+----+----------+----------+-----+
What I want retrieved is this:
+----+----------+----------+-----+
| id | fname | lname | cnt |
+----+----------+----------+-----+
| 2 | anna | nyugen | 2 |
| 5 | michaela | king | 3 |
| 7 | michaal | hardy | 3 |
+----+----------+----------+-----+
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 :)
310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
