postgresql - 根据表值合并两列

Question

我正在尝试将法语字符串(语言 ID 1)合并到一列中。到目前为止，我能够在 table1.title 和 table2.translated_topic 中获取法语字符串，但不确定如何连接它们。

版本:Postgres 9.6.0

源表架构:

表 1:knowledgebase_topics

id | title            | language_id |
------------------------------------
64   | The Topic      |    91       |  
65   | The Topic 2    |    91       |           
62   | Le fav sujet   |     1       |          
63   | Le fav sujet 2 |     1       |          
61   | le bonjour     |     1       |     

表 2:knowledgebase_topics_translations

id | translated_topic| knowledgebase_topic_id | language_id |
-------------------------------------------------------------
   | Le sujet        |          64            |     1       | 
   | Le sujet 2      |          65            |     1       |
   | Fav The Topic   |          62            |     91      |
   | Fav The Topic 2 |          63            |     91      |

---

给定以下查询:

SELECT title, translated_topic, "kbt".language_id, "kbtt".language_id
FROM knowledgebase_topics as "kbt" 
LEFT JOIN knowledgebase_topics_translations as "kbtt" on ("kbtt".knowledgebase_topic_id = "kbt".id) 
INNER JOIN knowledgebase_topics_organizations as "kbto" on ("kbto".knowledgebase_topic_id = "kbt".id) 
WHERE "kbto"."organization_id" = 1 
AND to_tsvector("kbt".title) @@ to_tsquery('le') 
OR to_tsvector("kbtt".translated_topic) @@ to_tsquery('le') 
AND "kbt".language_id = 1 
OR "kbtt".language_id = 1;

我得到以下结果:

     title      | translated_topic | language_id | language_id 
----------------+------------------+-------------+-------------
 The Topic      | Le sujet         |          91 |           1
 The Topic 2    | Le sujet 2       |          91 |           1
 Le fav sujet   | Fav The Topic    |           1 |          91
 Le fav sujet 2 | Fav The Topic 2  |           1 |          91
 le bonjour     |                  |           1 |     

期望的结果:table1.title 和table2.translated_topics 已根据language_id == 1 合并.两个表都有一个语言 ID 列。

     title      | language_id
----------------+--------------
 Le sujet       | 1
 Le sujet 2     | 1
 Le fav sujet   | 1
 Le fav sujet 2 | 1
 le bonjour     | 1

我该怎么做？

---

注意:我不是单纯的想查lang IDs = 1，比如

and "kbt".language_id = 1 AND (instead of OR) "kbtt".language_id = 1;

因为这会导致语言 ID 1 的表 2 中缺少 2 条记录:

     title      | translated_topic | language_id | language_id 
----------------+------------------+-------------+-------------
 Le fav sujet   | Fav The Topic    |           1 |          91
 Le fav sujet 2 | Fav The Topic 2  |           1 |          91
 le bonjour     |                  |           1 |          

---

那么，我已经让它工作了......但这性能好吗？

SELECT title, "kbt".language_id
FROM knowledgebase_topics as "kbt" 
INNER JOIN knowledgebase_topics_organizations as "kbto" on ("kbto".knowledgebase_topic_id = "kbt".id) 
WHERE "kbto"."organization_id" = 1
AND to_tsvector("kbt".title) @@ to_tsquery('le') 
AND "kbt".language_id = 1 
UNION ALL
SELECT translated_topic, "kbtt".language_id 
FROM knowledgebase_topics_translations as "kbtt"
INNER JOIN knowledgebase_topics_organizations as "kbto" on ("kbto".knowledgebase_topic_id = "kbtt".id) 
WHERE "kbto"."organization_id" = 1
AND to_tsvector("kbtt".translated_topic) @@ to_tsquery('le')
AND "kbtt".language_id = 1;

给出输出:

     title      | language_id 
----------------+-------------
 le bonjour     |           1
 Le fav sujet   |           1
 Le fav sujet 2 |           1
 Le sujet       |           1
 Le sujet 2     |           1
(5 rows)

Answer 1

设置回答问题的环境

首先，观察我们如何使用简洁的 DDL 最好地描述问题。最好在未来，你会学习如何写这样的问题..

CREATE TEMPORARY TABLE knowledgebase_topics AS
SELECT * FROM ( VALUES
  (64,'The Topic',91),
  (65,'The Topic 2',91),
  (62,'Le fav sujet',1),
  (63,'Le fav sujet 2',1),
  (61,'le bonjour',1)
) AS t(knowledgebase_topic_id, title, language_id);

CREATE TEMPORARY TABLE knowledgebase_topics_translations AS
SELECT * FROM ( VALUES
  ('Le sujet'       ,64,1  ),
  ('Le sujet 2'     ,65,1  ),
  ('Fav The Topic'  ,62,91 ),
  ('Fav The Topic 2',63,91 )
) AS t(translated_topic, knowledgebase_topic_id, language_id);

然后您只需告诉我们您想要什么，我们就可以轻松搭建工作环境并回答您的问题。不需要英语!对我们双方都更轻松。

解决方案

这里我们使用 UNION ALL我们将其包装在 SELECT 中所以我们可以按 id 排序，并轻松地在一个地方更改 language您正在寻找的。

SELECT title, language_id
FROM (
  SELECT knowledgebase_topic_id, title, language_id
  FROM knowledgebase_topics
  UNION ALL
  SELECT knowledgebase_topic_id, translated_topic, language_id
  FROM knowledgebase_topics_translations
) AS t(id, title, language_id)
WHERE language_id = 1
ORDER BY id;

输出

     title      │ language_id 
────────────────┼─────────────
 le bonjour     │           1
 Le fav sujet   │           1
 Le fav sujet 2 │           1
 Le sujet       │           1
 Le sujet 2     │           1
(5 rows)