如何在Oracle SQL中使用case语句和||运算符

Question

有人能帮助解释下面sql语句的含义吗？

FROM
(
CSA.X_INITIATED_BY_PSID
||
CASE
WHEN (SELECT USER_FULLNAME
FROM TABLE_NAME
WHERE USER_PSID = CSA.X_INITIATED_BY_PSID) IS NULL
THEN ' - NA'
ELSE ' - '
||
(SELECT USER_FULLNAME
FROM TABLE_NAME
WHERE USER_PSID = CSA.X_INITIATED_BY_PSID
)
END INTITATED_BY

Answer 1

您没有指定表的结构，所以我使用了一个最小的例子：

SELECT dummy || ' ' || 
CASE  (SELECT count(*) FROM dual) 
WHEN 1 THEN 'one'
WHEN 2 THEN 'huh?' 
END AS x
FROM dual;
X one

Answer 2

这是FROM子句吗？我不这么认为。

如果你问它做什么，那么：它将CSA.X_INITIATED_BY_PSID列值与连接起来

• table_name.user_fullname如果有一行的USER_PSID等于前面提到的X_INITIATED_BY_PSID
• NA(如果没有此类行(

例如：

SQL> with
2  -- sample data
3  csa (x_initiated_by_psid) as
4    (select 1 from dual union all
5     select 2 from dual
6    ),
7  table_name (user_psid, user_fullname) as
8    (select 1, 'Little' from dual
9    )
10  -- your query
11  select c.x_initiated_by_psid ||' - '||
12    case when (select t.user_fullname from table_name t
13               where t.user_psid = c.x_initiated_by_psid
14              ) is null then 'NA'
15         else
16              (select t.user_fullname from table_name t
17               where t.user_psid = c.x_initiated_by_psid
18              )
19    end result
20  from csa c;
RESULT
-------------------------------------------------
1 - Little
2 - NA
SQL>

---

尽管如此，这可能是一个过于复杂的解决方案，因为外部连接只需较少的努力即可完成相同的操作：

SQL> with
2  -- sample data
3  csa (x_initiated_by_psid) as
4    (select 1 from dual union all
5     select 2 from dual
6    ),
7  table_name (user_psid, user_fullname) as
8    (select 1, 'Little' from dual
9    )
10  -- your query
11  select c.x_initiated_by_psid ||' - '|| nvl(t.user_fullname, 'NA') result
12  from csa c left join table_name t on t.user_psid = c.x_initiated_by_psid;
RESULT
-------------------------------------------------
1 - Little
2 - NA
SQL>