Início / dba / Perguntas / 20661
Accepted
Mokus
Asked: 2012-07-11 11:15:19 +0800 CST

Como posso selecionar dados com uma consulta?

  • 772

Tenho as seguintes tabelas:

Mesaxwrh9_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 |
+----+----------+-------+-------------+---------------------+-------+-------+

Mesaxwrh9_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             |
+----+----------+-----------+-------+--------+----------------+

Tenho a seguinte consulta, é possível otimizar?

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;

A saída é a seguinte:

+------+----------+------+-------+--------+----------------+-------+
| 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 |
+------+----------+------+-------+--------+----------------+-------+
optimization

1 respostas

  1. Best Answer

    Não conheço a sintaxe do mysql, mas parece que o seguinte forneceria os mesmos resultados.

    SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, a.lang1 
FROM xwrh9_betty_dictionary_ids a 
LEFT JOIN xwrh9_betty_dictionary_words b ON b.id=a.lang1 OR b.id=a.lang2
WHERE b.id<>19
GROUP BY b.id;
    • 4

relate perguntas