例如,我在t_shops中有 3 个非空列:id、name、locale。区域设置列的默认值为 " " 空字符串。如果 where 语句不匹配,我需要选择默认值。我正在尝试使用 COALESCE 函数,但它仅适用于空列。我有PostgreSQL DB。
SELECT locale FROM t_shops WHERE id = '123' locale = 'not what i have in DB'
限制 1
SELECT locale
FROM t_shops
WHERE id = '123' AND locale = 'not what i have in DB'
UNION ALL
SELECT 'default locale goes here'
LIMIT 1 ; -- t_shops.locale if available, else default
合并 + 子选择
SELECT COALESCE(
( SELECT locale
FROM t_shops
WHERE id = '123' AND locale = 'not what i have in DB'
-- LIMIT 1, if required; depends on UNIQUE (locale) or not
),
'default locale goes here' -- 'default'::locale with type possible
);
对评论的回应
实际上,我不知道如何回复您的评论,因为我不知道您想做什么。
您能否发布product
表的表架构?像这样:
CREATE TABLE product (id SERIAL PRIMARY KEY,
locale TEXT NOT NULL,
other_column INT NOT NULL);
并用普通的英语单词描述你想做什么。也许你甚至不需要两次左加入到同一个桌子......