时间戳

首页 > 解决方案 > 基于行条件的 SQL 连接

sql - 基于行条件的 SQL 连接

问题描述

我有一张如下表。

+--------+------------+------+-----------------+
| key    | english    | lang | translation     |
+--------+------------+------+-----------------+
| id1    | string1    | eng  | NULL            |
| id1    | string1    | rus  | russian_string1 |
| id2    | string2    | eng  | NULL            |
| id2    | string2    | rus  | russian_string2 |
| id3    | string3    | eng  | NULL            |
| id3    | string3    | rus  | NULL            |
| id4    | string4    | eng  | NULL            |
| id5    | string5    | eng  | NULL            |
+--------+------------+------+-----------------+

我想以这样的方式查询，以便输出以下结果

+--------+------------+------+------------------+
| pk     | english    | lang | translation      |
+--------+------------+------+------------------+
| id1    | string1    | rus  | russian_string1  |
| id2    | string2    | rus  | russian_string2  |
| id3    | string3    | eng  | NULL             |
| id4    | string4    | eng  | NULL             |
| id5    | string5    | eng  | NULL             |
+--------+------------+------+-------------------+

为了实现这一点，我目前正在进行两个数据库调用，如下所示

SELECT key, english, lang, translation FROM table where lang = 'eng';
+--------+------------+------+-------------+
| key    | english    | lang | translation |
+--------+------------+------+-------------+
| id1    | string1    | eng  | NULL        |
| id2    | string2    | eng  | NULL        |
| id3    | string3    | eng  | NULL        |
| id4    | string4    | eng  | NULL        |
| id5    | string5    | eng  | NULL        |
+--------+------------+------+-------------+

和

SELECT key, english, lang, translation FROM table WHERE lang = 'rus' and translation is not null;
+--------+------------+------+-----------------+
| key    | english    | lang | translation     |
+--------+------------+------+-----------------+
| id1    | string1    | rus  | russian_string1 |
| id2    | string2    | rus  | russian_string2 |
+--------+------------+------+-----------------+

然后在代码中组合这两个查询的结果。我确信可以有更好的 SQL 方式来执行此操作。

标签： sqljoin

解决方案

一种方法是：

select t.*
from t
where lang = 'rus' or
      (lang = 'eng' and
       not exists (select 1 from t t2 where t2.key = t.key and t2.lang = 'rus');

一种更通用的方法 - 对于更多语言 - 使用row_number()：

select t.*
from (select t.*,
             row_number() over (partition by key order by case lang when 'rus' then 1 when 'eng' then 2 else 3 end) as seqnum
      from t
     ) t
where seqnum = 1;

推荐阅读