sqlite:有没有办法在 SELECT 子句中创建条件 OR/COALESCE? [英] sqlite: is there a way to make a conditional OR / COALESCE in the SELECT clause?

问题描述

我有以下观点:

CREATE VIEW FilmTableView AS

    SELECT  (TitleSP || " / " || TitleEN) as Title,  
            CompanyName, 
            CoverURI,
            CompanyFilmRelation.CompanyId,
            CompanyFilmRelation.FilmId 

    FROM    Film
    JOIN    CompanyFilmRelation on CompanyFilmRelation.FilmId = Film.FilmId 
    JOIN    Company on CompanyFilmRelation.CompanyId = Company.CompanyId 

    ORDER BY Title;

但我最终可能会得到 TitleSP 或 TitleEN 为空的记录.在这种情况下，我只想包含不为空的列，而不包含/".

But I might end up with records where either TitleSP or TitleEN are empty. In such case, I'd like to only include whichever column is not null and not include the "/".

有没有办法做到这一点?也就是说，遵循以下逻辑的东西:

Is there a way to do this? That is, something following the logic of:

if(TitleSP && TitleEN)
   select (TitleSP || " / " || TitleEN) as Title
else
   select (TitleSP ? TitleSP : TitleEn) as Title

推荐答案

SELECT CASE WHEN (TitleSP = '' OR TitleSP IS NULL)
                THEN COALESCE(TitleEN, '')
            WHEN (TitleEN = '' OR TitleEN IS NULL)
                THEN TitleSP
            ELSE (TitleSP || ' / ' || TitleEN)
       END AS Title, 
...

这篇关于sqlite:有没有办法在 SELECT 子句中创建条件 OR/COALESCE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！