sqlite:有没有办法在SELECT子句中进行条件OR / COALESCE？

Question

我有以下的看法：

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为空的记录。在这种情况下，我只想包括任何不为空的列，并且不包括"/“。

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

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

Answer 1

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, 
...

Answer 2

在sql中，如果一个运算符是null，那么表达式就变成null，您可以将其与coalesce函数一起使用来轻松解决此问题。像这样的东西应该可以工作(nb未测试)：

   COALESCE(TitleSP || " / " || TitleEN,TitleSP,TitleEN,"No Title") as Title

如果你不能这样做，那么case是可行的，就像这样(也可以处理空字符串)：

  TitleSP || 
  CASE WHEN ((ISNULL(TRIM(TitleSP),"") = "") OR (ISNULL(TRIM(TitleEN),"") = "")) THEN "" ELSE " / " END || 
  TitleEN as Title

Answer 3

SELECT COALESCE(
                NULLIF(TitleSP, '') || ' / ' || NULLIF(TitleEN, ''), 
                NULLIF(TitleSP, ''), 
                NULLIF(TitleEN, ''), 
                ''
               ), 
...