mysql - 自连接查找行位置

Question

我有一个单词表和另一个包含每个单词中使用的所有字符的表。例如如果有单词“test”，那么单词字符表将有 4 行 t,e,s,t。

mysql> select * from word_list;
+---------+---------+
| word_id | word    |
+---------+---------+
|       1 | This    |
|       2 | is      |
|       3 | test    |
|       4 | message |
|       5 | for     |
+---------+---------+
5 rows in set (0.00 sec)

mysql> select * from word_chars;
+----+---------+----------+-----------+
| id | word_id | char_seq | word_char |
+----+---------+----------+-----------+
|  1 |       1 |        1 | T         |
|  2 |       1 |        2 | h         |
...
...

| 19 |       5 |        2 | o         |
| 20 |       5 |        3 | r         |
+----+---------+----------+-----------+
20 rows in set (0.00 sec)

“e”在第二位的单词很容易找到。但是，如何找到“e”在第二个位置 和“a”在第五个位置 的单词？可以有多个(最多 8 个)这样的条件。

select word from word_list as a inner join word_chars as b on a.word_id = b.word_id 
where word_char = 'e' and char_seq = '2';
+---------+
| word    |
+---------+
| test    |
| message |
+---------+
2 rows in set (0.00 sec)

这是相关的表格。

drop table if exists word_list;
create table word_list (word_id int not null auto_increment, word varchar(255), primary key (word_id)) ENGINE=InnoDB;
insert into word_list (word) values ('This'), ('is'), ('test'), ('message'), ('for');

drop table if exists word_chars;
create table word_chars (id int not null auto_increment, word_id int, char_seq int, word_char varchar(50), primary key (id), unique key `word_seq` (word_id, char_seq), foreign key (word_id) references word_list(word_id)) ENGINE=InnoDB;
insert into word_chars (word_id, char_seq, word_char) values (1, 1, 'T'), (1, 2, 'h'), (1, 3, 'i'), (1, 4, 's'), (2, 1, 'i'), (2, 2, 's'), (3, 1, 't'), (3, 2, 'e'), (3, 3, 's'), (3, 4, 't'), (4, 1, 'm'), (4, 2, 'e'), (4, 3, 's'), (4, 4, 's'), (4, 5, 'a'), (4, 6, 'g'), (4, 7, 'e'), (5, 1, 'f'), (5, 2, 'o'), (5, 3, 'r')

更新:

是否可以在同一个查询中返回 count 或 max(char_seq)？所以对于前。在下面的答案中，它应该看起来像...

+---------+
| word    | count
+---------+
| message |  7
+---------+

Answer 1

select word from word_list as a
inner join word_chars as b on a.word_id = b.word_id
inner join word_chars as c on a.word_id = c.word_id
where b.word_char = 'e' and b.char_seq = '2'
and   c.word_char = 'a' and c.char_seq = '5';

结果:

+---------+
| word    |
+---------+
| message |
+---------+

无法立即想到一种优雅的方式来处理任何数量的可能情况 - 也许其他人对此有想法。