代码之家 › 专栏 › 技术社区 › Daebak

Mysql将回退语言的连接保留为或条件

fallback coalesce left-join mysql

Daebak · 技术社区 · 10 年前

我有两个表“countries”和“counties_lang”

+------+--------------+-----------------+
| id   | region_code  | sub_region_code |
+------+--------------+-----------------+
|    1 | 142          | 034             |
|    2 | 150          | 154             |
|    3 | 002          | 015             |
+------+--------------+-----------------+

+-----+--------------+---------------+--------------+
| id  | pid  | lang_code | lang_name | lang_capital |
+-----+------------------+-----------+--------------+
|   1 | 1    | en_GB     |Canada     | Ottawa       |
|   2 | 1    | de_DE     |Kanada     | Ottawa       |
|   3 | 2    | en_GB     |Italy      | Rome         |
+-----+------+-----------+-----------+--------------+

en_GB 是后备语言,所以我的目标是加入countrieslang,如果不在表中,则传递一个lnaguage,返回后备语言值

所选语言是 de_DE

+------+--------------+-----------------+--------------+
| id   | region_code  | lang_name       | lang_capital |
+------+--------------+-----------------+--------------+
|    1 | 142          | Kanada          | Ottawa       +
|    3 | 150          | Italy           | Rome         + (fallback language)
+------+--------------+-----------------+--------------+

我尝试过:

    Select * FROM countries AS c 
     LEFT JOIN ( 
        SELECT pid, 
            COALESCE(
                (
                    SELECT lang_name FROM countries_lang WHERE lang_code = 'de_DE' AND pid = c.id
                ),
                (
                    SELECT lang_name FROM countries_lang WHERE lang_code = 'en_GB' AND pid = c.id

                )
            ) AS cl_name,
            COALESCE(
                (
                    SELECT lang_capital FROM countries_lang WHERE lang_code = 'de_DE' AND pid = c.id
                ),
                (
                    SELECT lang_capital FROM countries_lang WHERE lang_code = 'en_GB' AND pid = c.id

                )
            ) AS cl_capital
        FROM countries_lang
    ) AS cl 

    ON (c.id = cl.pid)

但返回 c.id 未知列

谢谢大家!!!

编辑

我也尝试过这种方法,但总是返回回退语言值

    Select * FROM countries AS c
    LEFT JOIN ( 
        SELECT pid, lang_name, lang_code FROM countries_lang WHERE lang_code = 'de_DE' AND lang_code IS NOT NULL OR lang_code = 'en_GB'
    ) AS cl ON 
    c.id = cl.pid

2 回复 | 直到 6 年前

Tin 10 年前

查询:

select cl.id, c.region_code, cl.lang_name, cl.lang_capital
from countries_lang cl 
join countries c on c.id = cl.pid
left join 
(select cl.id, cl.pid
from countries_lang cl 
join countries c on c.id = cl.pid and cl.lang_code = 'de_DE') fcl on cl.pid = fcl.pid 
where cl.id = fcl.id or fcl.id is null

结果:

+------+--------------+-----------------+--------------+
| id   | region_code  | lang_name       | lang_capital |
+------+--------------+-----------------+--------------+
|    2 | 142          | Kanada          | Ottawa       +
|    3 | 150          | Italy           | Rome         + 
+------+--------------+-----------------+--------------+

Daebak 10 年前

好的,找到了解决办法,希望对别人有所帮助!

SELECT * FROM countries AS c
LEFT JOIN ( 
    SELECT jc.id, countries_lang.pid, countries_lang.lang_code,

    COALESCE(
        (
            SELECT lang_name FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'de_DE'

        ),
        (
            SELECT lang_name FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'en_GB'

        )
    ) AS coal_lang_name,

    COALESCE(
        (
            SELECT lang_capital FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'de_DE'

        ),
        (
            SELECT lang_capital FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'en_GB'

        )
    ) AS coal_lang_capital

    FROM countries_lang

    join countries jc on jc.id = countries_lang.pid
) AS cl ON 
(c.id = cl.pid)

要应用于所有语言,只需设置2个会话变量,一个用于fallback_language,另一个用于所选语言

实例

$fallback_lang=$_SESSION['en_GB'];或任何其他

并更改上述代码

第一次聚结 WHERE countries_lang。pid=jc。id和countries_lang。lang_code='$lang'

第二次凝聚 WHERE countries_lang。pid=jc。id和countries_lang。lang_code=“$fallback_lang”

COALESCE获取第一个不为NULL的值,因此在选择之前比回退。

干杯