MySQL：如何从集合中删除行？
我有一个翻译请求： （表结构：
- line_id
- lang（“en”或“ru”）
- 文本
，因此，在表中它看起来像：
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] =>你好！
I have a request of translation:
(table structure:
- line_id
- lang ("en" or "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)
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.
How to delete from set #6 row, because we have translation in the next row. GROUP BY will kill #7 and save #6 row.
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我只是猜测你可能需要：
I'm just guessing you probably need:
按 lang desc 排序（所以 ru 位于顶部）然后按 line_id 分组怎么样？
How about sort by lang desc (so ru is on top) and then group by line_id?
从翻译表中排除原始语言
新版本的新描述
exclude the original language from the translation table
new version for new description
为了列出已翻译的行和未经翻译的原始行，您必须面对 MySql 中缺乏对访问同一表的子查询的支持。
在我看来，你可以划分问题：首先用一个 SELECT 列出没有翻译的原始行；然后用另一个 SELECT 列出翻译；最后使用UNION。
此致。
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.
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.
Best regards.