SQL 用自己的文本替换 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21570236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Replace NULL Values with Own text
提问by Kuan E.
I need to display the keyboard players from a list of bands, and I've been able to using the following SQL:
我需要从乐队列表中显示键盘手，并且我已经能够使用以下 SQL：
SELECT BAND.NAME AS Band_Name, KBPLAYER.NAME AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
SELECT M.NAME, MO.BID
FROM MEMBEROF MO, MEMBER M
WHERE MO.INSTRUMENT='keyboards'
AND M.MID=MO.MID
) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME
The above query displays the names of all the band and the keyboard player (if any) in that band, but I also want to display 'No KeyBoard Players' for those bands that don't have a keyboard player. How can I achieve this? Please let me know if you need me to furnish with details of the table structure.
上面的查询显示该乐队中所有乐队和键盘手（如果有）的名称，但我还想为那些没有键盘手的乐队显示“无键盘手”。我怎样才能做到这一点？如果您需要我提供表格结构的详细信息，请告诉我。
Update:Please note that I'm not able to use any of the SQL3 procedures(COALESCE, CASE, IF..ELSE). It needs to conform strictly to SQL3 standard.
更新：请注意，我无法使用任何SQL3 procedures( COALESCE, CASE, IF..ELSE)。它需要严格符合 SQL3 标准。
采纳答案by Kuan E.
I've decided to do it differently since I wasn't going anywhere with the above SQL. I'll appreciate if anyone has suggestions to make for the above SQL with the set constraints.
我决定用不同的方式来做，因为我不会用上面的 SQL 去任何地方。如果有人对设置约束的上述 SQL 提出建议，我将不胜感激。
SELECT
band.name AS Band_Name, 'NULL' AS Keyboard_Player
FROM
memberof
INNER JOIN
member
ON
memberof.mid = member.mid
FULL JOIN
band
ON
memberof.bid = band.bid
AND
instrument = 'keyboards'
WHERE
member.name IS NULL
UNION
SELECT
band.name AS Band_Name, member.name AS Keyboard_Player
FROM
memberof
INNER JOIN
member
ON
memberof.mid = member.mid
FULL JOIN
band
ON
memberof.bid = band.bid
WHERE
instrument = 'keyboards'
回答by harmic
回答by Naveen
As per your comment to replace empty string to some text, the simple solution is to use case statement like below.
根据您将空字符串替换为某些文本的评论，简单的解决方案是使用如下所示的 case 语句。
SELECT BAND.NAME AS Band_Name,
CASE WHEN KBPLAYER.NAME = '' THEN 'No Player' ELSE KBPLAYER.NAME END AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
SELECT M.NAME, MO.BID
FROM MEMBEROF MO, MEMBER M
WHERE MO.INSTRUMENT='keyboards'
AND M.MID=MO.MID
) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME
回答by tarzanbappa
Try this....
尝试这个....
SELECT BAND.NAME AS Band_Name,
ISNULL(NULLIF(KBPLAYER.NAME,''),'No KeyBoard Players') AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
SELECT M.NAME, MO.BID
FROM MEMBEROF MO, MEMBER M
WHERE MO.INSTRUMENT='keyboards'
AND M.MID=MO.MID
) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME
回答by Ram Dwivedi
If NVL is allowed, below should work.
如果允许 NVL，下面应该可以工作。
NVL(KBPLAYER.NAME, 'No KeyBoard Players')
