MySQL多行同一张表

Question

我有一个包含配置数据行的 MySQL 表，即:

id  item   value1  value2
2   class   ship    bow
3   class   car     tires
5   reg     ship    level1
7   reg     ship    level2
9   reg     car     level5

我正在尝试创建一个查询，该查询选择 item='class' 的所有行，并返回一个与初始查询匹配的数据集 + item='reg' 和 value1=value1(来自初始结果)的所有行。

因此，在这种情况下，结果集应如下所示:

class  ship  bow   reg   ship  level1
class  ship  bow   reg   ship  level2
class  car   tires reg   car   level5

我有点沮丧，希望这是有道理的。感谢您提供正确方向的任何指示!

Answer 1

您必须将表加入到其自身中。假设您的表名为 configs (因为你没有告诉我们它叫什么)，这样的事情应该有效:

select t1.item, t1.value1, t1.value2, t2.item, t2.value1, t2.value2
    from configs as t1
    inner join configs as t2
    on t2.value1 = t1.value1 and t2.item = 'reg'
    where t1.item = 'class';

如果您需要所有没有匹配的行 reg结果中也返回了行，更改 inner join至 left outer join .确保您在 item 上有索引和 value1如果您希望此查询正常执行。

这是一个快速的概念证明，显示了上述查询的工作原理:

mysql> create table configs (
    -> id int unsigned primary key auto_increment,
    -> item varchar(32) not null,
    -> value1 varchar(32) not null,
    -> value2 varchar(32) not null,
    -> index (item),
    -> index (value1)
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into configs (id, item, value1, value2) values
    -> (2, 'class', 'ship', 'bow'),
    -> (3, 'class', 'car', 'tires'),
    -> (5, 'reg', 'ship', 'level1'),
    -> (7, 'reg', 'ship', 'level2'),
    -> (9, 'reg', 'car', 'level5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from configs;
+----+-------+--------+--------+
| id | item  | value1 | value2 |
+----+-------+--------+--------+
|  2 | class | ship   | bow    |
|  3 | class | car    | tires  |
|  5 | reg   | ship   | level1 |
|  7 | reg   | ship   | level2 |
|  9 | reg   | car    | level5 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)

mysql> select t1.item, t1.value1, t1.value2, t2.item, t2.value1, t2.value2
    -> from configs as t1
    -> inner join configs as t2
    -> on t2.value1 = t1.value1 and t2.item = 'reg'
    -> where t1.item = 'class';
+-------+--------+--------+------+--------+--------+
| item  | value1 | value2 | item | value1 | value2 |
+-------+--------+--------+------+--------+--------+
| class | ship   | bow    | reg  | ship   | level1 |
| class | ship   | bow    | reg  | ship   | level2 |
| class | car    | tires  | reg  | car    | level5 |
+-------+--------+--------+------+--------+--------+
3 rows in set (0.00 sec)