问题

以下是您要翻译的内容：

I have two tables. Table V:

| City | Value |
| --- | --- |
| Default | 1.0 |
| New York | 4.5 |
| Chicago | 3.7 |

And table F:

| ID | City |
| --- | --- |
| 0321321 | Chicago |
| 6546545 | New York |
| 6654654 | Paris |
| 4547556. | London. |

I would like to left join table V onto table F. However, in Table V, the City column will not have all the cities that Table F may contain. So I would like to join on the city column, but be able to default on the Default value if the city does not exist. I tried the following hoping that if the first part of the coalesce returned false, the second part would work, but it does not. Any idea how I would perform this join?

SELECT F.ID, F.City, V.Value FROM F LEFT JOIN V ON COALESCE(V.city = F.city, V.city="Default")

Tried the code
SELECT F.ID, F.City, V.Value FROM F LEFT JOIN V ON COALESCE(V.city = F.city, V.city="Default")

but it only populated the cities that existed in table V

答案1

得分: 1

COALESCE函数只会返回其参数中的第一个非空值。

在你的情况下，你需要进行左连接，然后替换空值。

select f.id, f.city,
    case when v.value is null
        then (select value from v where city = 'Default')
        else v.value
    end as value
from f left join v on v.city = f.city

或者也可以尝试如下方式：

select
    distinct f.id,
    f.city,
    coalesce(v.value, d.value) as value
from
    f 
    left join
    v on v.city = f.city 
    join
    (select value from v where city = 'Default') as d on true

select f.id, f.city,
    case when v.value is null
        then select value from v where city = 'Default'
        else v.value
    end as value
from f left join v on v.city = f.city

select
    distinct f.id,
    f.city,
    coalesce(v.value, d.value) as value
from
    f 
    left join
    v on v.city = f.city 
    join
    v d on d.city = 'Default'