mysql - 查询在 MySQL 中工作正常但在 PostgreSQL 中不工作

Question

此查询在 MySQL 中运行良好，但在 PostgreSQL 中运行不正常:

select
concat(hc.id, hl.languagecode) AS id,
hc.id AS category_id,
hl.languagecode AS languagecode
from language hl
join category hc
left join categorytranslation hct ON hl.languagecode = hct.languagecode and hc.id = hct.category_id;

在 PostgreSQL 中，我有时会遇到错误:

ERROR: syntax error at end of input LINE 9: ....languagecode = hct.languagecode and hc.id = hct.category_id ^ Query failed PostgreSQL said: syntax error at end of input

或者一个空的结果。我正在使用 Postico 对其进行测试。

有什么问题吗？

我想做的是得到这样的结果:

 select
        concat(hc.id, hl.languagecode) AS id,
        hc.id AS category_id,
        hl.languagecode AS languagecode,
        hc.slug AS slug
from category hc, language hl

但我还想加入一个带有翻译“categorytranslation”的表格。如果翻译可用，则应采用该翻译而不是“类别”表。为此，我修改了这样的查询:

    SELECT
            concat(hp.id, hl.languagecode) AS id,
            hp.id AS id,
            hl.languagecode AS languagecode,
            hpt.languagecode as translang,
            CASE WHEN (hpt.languagecode is not null and hpt.slug is not null and trim(hpt.slug) <> '')
              THEN hpt.slug
              ELSE hp.slug
            END AS slug,
    from language hl
join category hc
left join categorytranslation hct ON hl.languagecode = hct.languagecode and hc.id = hct.category_id;

Answer 1

在 PostgreSQL 中，每个连接都需要一个连接条件短语。在您的查询中，表之间的关系似乎是 hl -> hct -> hc;然后查询变为:

SELECT hc.id || languagecode AS id,
       hc.id AS category_id,
       languagecode
FROM language hl
LEFT JOIN hapis_hatracocategorytranslation hct USING (languagecode)
JOIN category hc ON hc.id = hct.category_id;

请注意，通过在第一个连接条件中使用 USING 短语，两个表中的 languagecode 列之间没有歧义，因此您可以在选择列表中省略别名.