我在mysqlInnoDB 中有一個名稱表作為

Firstname        Lastname
Steve            Ross
John             Doe
J.               Doe
Mike             Ross
Michael          Ross
Roger            Jackson

我想選擇其中有重複的行first letter of Firstname + Lastname。

對於範例表，SELECT 查詢應返回

John             Doe
J.               Doe
Mike             Ross
Michael          Ross

提議的查詢

SELECT B.firstname,B.lastname FROM
(
   SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount
   FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1))
   HAVING COUNT(1) > 1
) A
INNER JOIN
(
   SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,
   firstname,lastname FROM names
) B USING (nametag);

您的樣本數據

mysql> DROP DATABASE IF EXISTS alldb;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE alldb;
Query OK, 1 row affected (0.00 sec)

mysql> USE alldb
Database changed
mysql> CREATE TABLE names
   -> (
   ->   id INT NOT NULL AUTO_INCREMENT,
   ->   lastname VARCHAR(25) NOT NULL,
   ->   firstname VARCHAR(25) NOT NULL,
   ->   PRIMARY KEY (id)
   -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names (firstname,lastname) VALUES
   -> ('Steve','Ross'),('John','Doe'),('J.','Doe'),
   -> ('Mike','Ross'),('Michael','Ross'),('Roger','Jackson');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM names;
+----+----------+-----------+
| id | lastname | firstname |
+----+----------+-----------+
|  1 | Ross     | Steve     |
|  2 | Doe      | John      |
|  3 | Doe      | J.        |
|  4 | Ross     | Mike      |
|  5 | Ross     | Michael   |
|  6 | Jackson  | Roger     |
+----+----------+-----------+
6 rows in set (0.00 sec)

mysql>

提議的查詢已執行

mysql> SELECT B.firstname,B.lastname FROM
   -> (
   ->     SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount
   ->     FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1))
   ->     HAVING COUNT(1) > 1
   -> ) A
   -> INNER JOIN
   -> (
   ->     SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,
   ->     firstname,lastname FROM names
   -> ) B USING (nametag);
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
| J.        | Doe      |
| Mike      | Ross     |
| Michael   | Ross     |
+-----------+----------+
4 rows in set (0.00 sec)

mysql>

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/97566