我有下表:
桌子xwrh9_betty_dictionary_ids
+----+----------+-------+-------------+---------------------+-------+-------+
| id | ordering | state | checked_out | checked_out_time | lang1 | lang2 |
+----+----------+-------+-------------+---------------------+-------+-------+
| 3 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 18 | 19 |
| 4 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 18 | 20 |
| 5 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 18 | 21 |
| 6 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 18 | 22 |
+----+----------+-------+-------------+---------------------+-------+-------+
桌子xwrh9_betty_dictionary_words
+----+----------+-----------+-------+--------+----------------+
| id | language | word | state | domein | characteristic |
+----+----------+-----------+-------+--------+----------------+
| 18 | en | sad | 1 | sad | sad |
| 19 | en | sub | 1 | am | al |
| 20 | hu | sub2 | 1 | wed | wed |
| 21 | ro | sub3 | 1 | sd | sd |
| 22 | en | sds | 1 | ss | sd |
| 23 | en | alamkorta | 1 | sd | sd |
+----+----------+-----------+-------+--------+----------------+
我有以下查询,是否可以优化?
SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, c.lang1
FROM xwrh9_betty_dictionary_ids as a
INNER JOIN ( SELECT lang1 FROM xwrh9_betty_dictionary_ids WHERE lang2=19) as c
LEFT JOIN xwrh9_betty_dictionary_words as b
ON ((a.lang2=b.id or b.id=c.lang1) AND b.id<>19)
WHERE a.lang1=c.lang1 GROUP BY id;
输出如下:
+------+----------+------+-------+--------+----------------+-------+
| id | language | word | state | domein | characteristic | lang1 |
+------+----------+------+-------+--------+----------------+-------+
| 18 | en | sad | 1 | sad | sad | 18 |
| 20 | hu | sub2 | 1 | wed | wed | 18 |
| 21 | ro | sub3 | 1 | sd | sd | 18 |
| 22 | en | sds | 1 | ss | sd | 18 |
+------+----------+------+-------+--------+----------------+-------+
我不知道 mysql 语法,但似乎以下内容会给您相同的结果。