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

I have a request of translation: (table structure:

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

line_id
line_id
lang ("en" or "ru")
lang（“en”或“ru”）
text
文本

so, in the table it looks like:

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

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

and all translations are in the same table with key line_id, that is independent from the language)

并且所有翻译都与关键字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';

it will return such array

它会返回这样的数组

> [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 - is original text, t - translation.

o - 是原始文本，t - 翻译。

How to delete from set #6 row, because we have translation in the next row. GROUP BY will kill #7 and save #6 row.

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

the best result would be:

最好的结果是：

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

without [o] => Hello! [t] => Hello!

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

4 个解决方案

#1

I'm just guessing you probably need:

我猜你可能需要：

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'

#2

How about sort by lang desc (so ru is on top) and then group by line_id?

怎么样按lang desc排序（所以ru在顶部）然后按line_id分组？

#3

exclude the original language from the translation table

从翻译表中排除原始语言

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' 
   AND ru.lang!='en'

new version for new description

新版本的新说明

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

#4

In order to list translated rows and original rows without translation, you must face the lack of support in MySql for sub queries that access the same table.

为了在没有转换的情况下列出已翻译的行和原始行，您必须面对MySql中缺少对访问同一个表的子查询的支持。

In my opinion you could divide the problem: first list original rows without translations with one SELECT; then list translations with another SELECT; in the end use a UNION.

在我看来，你可以分解问题：首先列出没有翻译的原始行与一个SELECT;然后用另一个SELECT列出翻译;最后使用UNION。

-- original without translations
SELECT txt.line_id, txt.text AS o, ru.text AS t 
FROM text AS txt LEFT JOIN text AS ru 
ON(txt.line_id = ru.line_id AND txt.lang = 'en' AND ru.lang != 'en')
WHERE ru.line_id IS NULL -- ensures no translations have been found

UNION

-- only matching translations
SELECT txt.line_id, txt.text AS o, ru.text AS t
FROM text AS txt JOIN text AS ru 
ON(txt.line_id = ru.line_id AND txt.lang = 'en' AND ru.lang != 'en');

Best regards.

最好的祝福。

#1