从 sqlite3 数据库中的多个相似记录中条件选择单个记录

Question

我正在开发使用Sqlite3数据库进行存储的android应用程序。

最近我遇到了一个问题，我需要根据对特定列的条件从多个相似的行中选择一行。

只是更具体...

源数据：

_id | code | language | data
 1  | 1    | English  | aaa
 2  | 1    | French   | bbb
 3  | 1    | Spanish  | ccc
 4  | 1    | Portuguse| ddd
 5  | 2    | English  | eee
 6  | 2    | French   | fff
 7  | 3    | English  | ggg
 8  | 4    | French   | hhh

数据库中的多个条目可以具有相同的代码值和不同的语言值。

我的条件如下：

1. 我应该至少有一个每个代码值的记录。

我

2. 必须根据我想要的语言进行搜索。假设如果我将语言作为西班牙语，我应该得到所有西班牙语记录。

3. 如果任何代码条目没有我给出的给定语言，那么我应该得到该代码的英文条目。

4. 如果任何代码条目只有一个语言的记录，那么无论我给出的语言如何，我都应该得到该记录。

我从上表中得出的预期结果：

如果我把语言作为西班牙语：-

3  | 1    | Spanish  | ccc
5  | 2    | English  | eee
7  | 3    | English  | ggg
8  | 4    | French   | hhh

1. 我应该有 1,2,3,4 个代码值。

2. 使用带有代码 1 的语言西班牙语，我有西班牙语的记录，所以它应该来了。

3. 使用带有代码 2 的语言西班牙语我没有西班牙语的记录，因此应选择带有代码 2 和语言英语的记录。

4. 代码3和4记录只有一个条目，所以这些记录应该来。

我在网上搜索了很多关于如何在类似行上操纵这些条件的信息，但我无法得到答案。有人可以帮助我查询预期结果吗？

提前谢谢。

Answer 1

如果您需要在一个怪物查询中完成它，这里有一种基于外部连接多个子查询的方法。 假设：(code, language)是唯一键;所有列都NOT NULL。

---

首先，规则 1 说每个代码都需要在最终结果中表示。 让我们all_codes成为提供每个唯一代码的子查询：

(select code 
   from my_table 
  group by code) all_codes

接下来，规则 2 规定需要存在具有所需语言的任何行。 matches 成为提供这些行的子查询：

(select _id, code, language, data 
   from my_table 
  where language = ?) matches

接下来，规则 3 说在没有所需语言的情况下，回退到英语。 让en_fallbacks成为提供这些行的子查询：

(select _id, code, language, data 
   from my_table 
  where language = 'English') en_fallbacks

规则 4 说，特定代码的任何单例记录都应存在于结果中。 让我们singletons提供这些行的子查询：

(select MIN(_id) as _id, code, MIN(language) as language, MIN(data) as data 
   from my_table 
  group by code 
 having (MIN(_id) = MAX(_id))) singletons

---

最后，在共享code列上按顺序将它们连接在一起，并使用COALESCE获取所需的列值：

select all_codes.code,
       COALESCE(matches._id, en_fallbacks._id, singletons._id) as _id,
       COALESCE(matches.language, en_fallbacks.language, singletons.language) as language,
       COALESCE(matches.data, en_fallbacks.data, singletons.data) as data
  from (...) all_codes
       left outer join (...) matches on (all_codes.code = matches.code)
       left outer join (...) en_fallbacks on (all_codes.code = en_fallbacks.code)
       left outer join (...) singletons on (all_codes.code = singletons.code)

警告：我尚未测试这些查询的正确性或性能。