要回答这里的问题是查询

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

我根据盯着输出提出了这个查询

如果有一个名为 name_country 的表可能会像这样：

CREATE TABLE name_country
SELECT A.id name_id,B.id country_id
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
ALTER TABLE name_country ADD PRIMARY KEY (name_id,country_id);
ALTER TABLE name_country ADD UNIQUE INDEX (country_id,name_id);

这样一来，您可以维护 name_country 表，使得 name 和 country 在原始表中不必是 1 个 id。您可以自由地批量输入姓名。稍后，附加国家/地区值。

一旦你可以创建一个 name_country 表，你就可以像这样进行连接：

SELECT B.id,B.name,C.id,C.name
FROM name_country A
INNER JOIN mytable B ON A.name_id = B.id
INNER JOIN mytable C ON A.country_id = C.id;

更新 2012-08-15 15:04 EDT

我首先建议的查询

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

是答案

首先是您的示例数据

DROP DATABASE IF EXISTS cabita;
CREATE DATABASE cabita;
USE cabita
CREATE TABLE mytable 
(id int not null auto_increment,
name varchar(20),primary key (id));
insert into mytable (name) values ('name1'),('country1');
insert into mytable (name) values ('name2'),('country2');
select * from mytable;

这是您加载的示例数据

mysql> DROP DATABASE IF EXISTS cabita;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> USE cabita
Database changed
mysql> CREATE TABLE mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into mytable (name) values ('name1'),('country1');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mytable (name) values ('name2'),('country2');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+----------+
| id | name     |
+----+----------+
|  1 | name1    |
|  2 | country1 |
|  3 | name2    |
|  4 | country2 |
+----+----------+
4 rows in set (0.01 sec)

这是我执行的原始查询

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.03 sec)

mysql>

虽然查询适用于您的示例数据，但您必须密切注意名称与国家/地区的关系。您必须确保三件事才能让我的查询工作

1. name的id是奇数
2. 国家的id是偶数
3. name 的 id 比 country 的 id 少一个

这是我能够利用的一种关系。

在您的示例数据中还可以看到另一种关系：每个字段末尾的数字。如果您可以批量输入姓名，然后批量加载国家/地区，则您将不得不依赖每个姓名字段末尾的数字。这样的查询看起来像这样：

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE A.name > B.name;

并将执行如下

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE A.name > B.name;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

或者这个查询

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';

这将执行如下

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE LEFT(A.name,4)='name'
    ->     AND LEFT(B.name,7)='country';
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

所有这些查询都对数据的内容做出假设。这样的查询会产生很差的性能结果。这就是为什么我建议创建一个外部表来执行将 name 的 id 与其国家/地区耦合在一起的 JOIN。

更新 2012-08-16 17:19 EDT

回答评论中的最后一个问题

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN mytable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';

如果描述涉及不同的表，那么可能是：

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN difftable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';