MySQL:如何从集合中删除行？

Question

我有翻译请求:(表结构:

1. 线路编号
2. lang(“en”或“ru”)
3. 正文

所以，在表中它看起来像:

5 | en | Test
6 | en | Hello!
7 | en | Another words
6 | ru | Привет!

并且所有翻译都在同一个表中，键 line_id，与语言无关)

SELECT txt.line_id, txt.text AS o, ru.text AS t 
  FROM text AS txt, text AS ru 
 WHERE txt.line_id = ru.line_id 
   AND txt.lang = 'en';

它将返回这样的数组

> [5] => Array ( [line_id] => 5 [o] => Test [t] => Test 
> [6] => Array ( [line_id] => 6 [o] => Hello! [t] => Hello! ) 
> [7] => Array ( [line_id] => 6 [o] => Hello! [t] => Привет! )

o - 原文，t - 翻译。

如何从第 6 行中删除，因为我们在下一行有翻译。 GROUP BY 将杀死#7 并保存#6 行。

最好的结果是:

> [5] => Array ( [line_id] => 5 [o] => Test [t] => ) 
> [6] => Array ( [line_id] => 6 [o] => Hello! [t] => Привет! )

没有 [o] => 你好! [t] => 你好!

Answer 1

我猜你可能需要:

SELECT txt.line_id, 
         txt.text AS o, 
         COALESCE (ru.text,'UNTRANSLATED') AS t 
FROM 
  text AS txt
LEFT JOIN 
  text AS ru 
ON txt.line_id=ru.line_id AND ru.lang='ru'
WHERE txt.lang='en'