postgresql COALESCE内部的位置

sdnqo3pr 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(98)

我想返回与id和language_code都匹配的所有topics_i18n.content_i18n，但如果id存在，但在保存language_code的另一个表中没有对应的行，则返回topics.content。
我试过用“COALESCE”

SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT JOIN topics_i18n
  ON topics.id = topics_i18n.topic_id
  WHERE section_id = $1 AND topics_i18n.language_code = $2
  ORDER BY position ASC

然而，这不起作用，我觉得我需要一个WHERE内的COALESCE？

SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n WHERE topics_i18n.language_code = $2, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT JOIN topics_i18n
  ON topics.id = topics_i18n.topic_id
  WHERE section_id = $1 
  ORDER BY position ASC

有什么建议可以帮助我解决这个问题吗？

postgresql

来源：https://stackoverflow.com/questions/74479902/where-inside-coalesce

1条答案

按热度按时间

c9x0cxw01#

将语言子表达式从where子句移到on条件中，它应该可以正常工作。我猜这实际上是业务逻辑。下面是经过修改的查询。

SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT OUTER JOIN topics_i18n
    ON topics.id = topics_i18n.topic_id 
   AND topics_i18n.language_code = $2
  WHERE topics.section_id = $1
  ORDER BY position ASC;

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql COALESCE内部的位置

1条答案

相关问题

热门标签

最新问答