GROUP BY 时的组内操作 [英] Operations within group when GROUP BY

问题描述

表items的键由(lang,id)组成.有些项目有不同语言的翻译名称.我想创建这样一个查询，该查询将以给定的语言返回数据库中的所有项目，并且如果某个项目没有翻译来返回未翻译的值.

The key of table items is comprised of (lang, id). Some items have a translated name in different languages. I want to create such a query that would return all the items from the DB in a given language and if an item doesn't have a translation to return the untranslated value.

假设我想要用保加利亚语编写所有项目.这就是我被困的地方:

Say I want to get all items written in Bulgarian. This is where I'm stuck:

SELECT lang, id, name
FROM items
WHERE lang = "bg" OR lang = "en"
GROUP BY id

问题出现在有一个项目，比如 (1, "en") 和一个项目 (1, "bg").id 是一样的.那么 MySQL 或 SQLite 如何确定返回哪个结果呢?有什么办法可以告诉它，如果存在，我宁愿返回 (1, "bg") 但如果不存在，则返回 (1, "en") 会让我满意吗?

The problem arises when there is an item, say, (1, "en") and an item (1, "bg"). The ids are the same. Then how does MySQL or SQLite determine which result to return? Is there any way I can tell it that I would rather prefer to return (1, "bg") if it exists but if it doesn't then (1, "en") would satisfy me?

附言为了进一步说明我想要什么，让我们假设数据库包含以下带有模式(id、lang、name)的条目:

P.S. To further illustrate what I want let's imagine that the database contains the following entries with schema (id, lang, name):

(1, "en", "abc")

(2, "en", "cde")

(3, "en", "def")

(1, "bg", "абв")

(3, "bg", "жзи")

在执行所需的保加利亚语查询后，我应该得到:

After executing the desired query for Bulgarian I should get:

(1, "bg", "абв")

(2, "en", "cde")

(3, "bg", "жзи")

推荐答案

如果未翻译"的意思是英语"，或者换句话说，基本语言是英语，您可以LEFT加入表格以本身并使用 COALESCE() 函数摆脱 NULL 值

If "untranslated" means "English" or in other words, the base language is English, you can LEFT join the table to itself and use COALESCE() function to get rid of NULL values

SELECT COALESCE(bg.lang, en.lang) AS lang
     , en.id                      AS id
     , COALESCE(bg.name, en.name) AS name
FROM items en
  LEFT JOIN items bg
    ON  bg.id = en.id
    AND bg.lang = 'bg'
WHERE en.lang = 'en'

这篇关于GROUP BY 时的组内操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！