问题描述
+----+------------+------+
| id | title | lang |
+----+------------+------+
| 1 | title 1 EN | en |
| 1 | title 1 FR | fr |
| 1 | title 1 ZH | zh |
| 2 | title 2 EN | en |
| 3 | title 3 ZH | zh |
+----+------------+------+
这是我的表格,我想按ID分组,但有时我需要使用语言"en"来优先,有时我需要使用语言"zh"作为优先权
this is my table and I want to group by id but I sometimes I need language "en" to have priority and sometimes I need to have language "zh" as priority
SELECT * FROM table GROUP BY id
给了我所有uniqe ID的列表,但是将zh替换为id 1,是否可以为语言添加优先级?
gives me a list of all uniqe ids but places zh in favor for id 1, is it possible that I can add a priority for language?
"en"的期望输出:
+----+------------+------+
| id | title | lang |
+----+------------+------+
| 1 | title 1 EN | en |
| 2 | title 2 EN | en |
| 3 | title 3 ZH | zh |
+----+------------+------+
我想要的"fr"输出:
My desired output for "fr":
+----+------------+------+
| id | title | lang |
+----+------------+------+
| 1 | title 1 FR | fr |
| 2 | title 2 EN | en |
| 3 | title 3 ZH | zh |
+----+------------+------+
我想要的..不停地:)
my desired .. on and on :)
推荐答案
自SQLite 3.7.11起,您可以使用 MAX
或 MIN 选择返回组中的哪条记录.代码>.像
lang ='en'
这样的布尔表达式将返回0或1:
Since SQLite 3.7.11, you can select which record in a group is returned by using MAX
or MIN
.A boolean expression like lang = 'en'
returns either 0 or 1:
SELECT *,
MAX(lang = 'en')
FROM MyTable
GROUP BY id
如果您想优先使用一种以上的语言,则可以使用 CASE表达式:
If you want to have priorities for more than one language, you can use a CASE expression:
SELECT *,
MAX(CASE lang
WHEN 'en' THEN 2
WHEN 'fr' THEN 1
ELSE 0
END)
FROM MyTable
GROUP BY id
这篇关于Sqlite GROUP BY优先级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!