Sqlite GROUP BY优先级 [英] Sqlite GROUP BY priority

问题描述

+----+------------+------+
| 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优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！