如果名稱為 NULL，我只想讓訊息“沒有演員”。我怎樣才能做到這一點？
SELECT length, title, LISTAGG(SUBSTR(first_name, 1, 1) || '. ' || last_name, ', ')
WITHIN GROUP (ORDER BY last_name) AS ACTORS -- 'no actors' if first_name is NULL
FROM film INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
WHERE release_year = 1991
GROUP BY title, length
ORDER BY length DESC;
我已經在 LISTAGG 函式中使用 NVL 進行了嘗試，但我無法擺脫“。”這一點。的串聯。還有另一種方法可以做到這一點嗎？
uj5u.com熱心網友回復：
使用CASE運算式：
SELECT length,
title,
LISTAGG(
CASE
WHEN first_name IS NOT NULL
THEN SUBSTR(first_name, 1, 1) || '. ' || last_name
ELSE 'no actors'
END,
', '
) WITHIN GROUP (ORDER BY last_name)
AS ACTORS -- 'no actors' if first_name is NULL
FROM film
INNER JOIN film_actor
USING (film_id)
INNER JOIN actor
USING (actor_id)
WHERE release_year = 1991
GROUP BY title, length
ORDER BY length DESC;
uj5u.com熱心網友回復：
您可以使用DECODE(), 或NVL2():
SELECT length,
title,
DECODE (first_name,
NULL, 'No actors',
LISTAGG (...)) AS actors
...
SELECT length,
title,
NVL2 (first_name,
LISTAGG (...),
'No actors') AS actors
...
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/530918.html
標籤：sql甲骨文
上一篇：查找沒有員工的部門