SQL Oracle：用单词替换空结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12003957/
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 Oracle: Replace an empty result with word
提问by John Smithv1
I'm working on this problem for several days. I have a oracledatabase. The problem must be resolved in one query. No Function, Pocedure, ... I want to make a select. When he has results, post them. Else there should be "empty result".
我正在解决这个问题好几天了。我有一个oracle数据库。该问题必须在一次查询中解决。No Function, Pocedure, ... 我想做一个选择。当他有结果时，发布它们。否则应该有“空结果”。
select case
when count(*) = 0
then 'no Entry'
else MAX(Member)--all Members should be here
END as Member
from tableMember
where Membergroup = 'testgroup';
The problem is that Oracle wants an Agregat function by the else. So I only get one value if the result is not "no entry". I need all values.
问题是Oracle 需要else 的Agregat 函数。所以如果结果不是“无条目”，我只会得到一个值。我需要所有的价值观。
Everybody who can help me is welcome and makes me happy.
欢迎所有可以帮助我的人，并让我感到高兴。
回答by schurik
not sure what do you try to achieve, perhaps this
不知道你想达到什么目的，也许这个
select member from tablemember where Membergroup = 'testgroup'
union
select 'no Entry'
from dual
where NOT EXISTS ( select member from tablemember where membergroup = 'testgroup')
;
回答by Ben
There's no need for two aggregate queries, you just need to check whether max(member)is null. I'd do it this way to make it clear what's going on.
不需要两个聚合查询，你只需要检查是否max(member)为空。我会这样做是为了弄清楚发生了什么。
select case when max_member is null then 'no entry' else max_member end as member
from ( select max(member) as max_member
from tablemember
where membergroup = 'testgroup'
)
If, however, you want to return all members you can do something like the following:
但是，如果您想返回所有成员，您可以执行以下操作：
select member
from tablemember
where membergroup = 'testgroup'
union all
select 'no entry'
from dual
where not exists ( select 1 from tablemember where membergroup = 'testgroup')
回答by Leigh Riffel
If you RIGHT JOINyour query with a query for the empty set you will always get one row and will get all the rows if your query returns data. This is less expensive (faster) than a UNIONor UNION ALLwith a NOT EXISTSbecause it does not require multiple scans of the data.
如果您RIGHT JOIN的查询使用空集查询，您将始终获得一行，如果您的查询返回数据，则将获得所有行。这比 aUNION或UNION ALLa更便宜（更快），NOT EXISTS因为它不需要对数据进行多次扫描。
SELECT nvl(a.member,b.member) member
FROM (SELECT member FROM tablemember WHERE membergroup='????') a
RIGHT JOIN (SELECT 'no Entry' member FROM dual) b ON 1=1;
Test Environment:
测试环境：
DROP TABLE tablemember;
CREATE TABLE tablemember AS
(
SELECT TO_CHAR(level) member
, DECODE(mod(level, 5), 0, 'testgroup', 'othergroup') membergroup
FROM dual CONNECT BY level <= 50
);
回答by Ilya Slyisarenko
You can use some aggregate functions and NVL for achieve you goal:
您可以使用一些聚合函数和 NVL 来实现您的目标：
SELECT MIN('VALUE 1') AS p1, MIN('VALUE 2') AS p2 FROM DUAL WHERE 1=0
SELECT MIN('VALUE 1') AS p1, MIN('VALUE 2') AS p2 from DUAL WHERE 1=0
result of this query is: NULL, NULL
此查询的结果是：NULL, NULL
next, replace empty values by desired strings:
接下来，用所需的字符串替换空值：
SELECT
NVL(MIN('1'), 'empty value 1') AS p1,
NVL(MIN('STRING VALUE'), 'empty value 2') AS p2,
NVL(MIN((select 'subquery result' from dual)), 'empty subquery result') as p3
FROM
DUAL
WHERE
1=0
But, you can't mix numbers and strings in fields.
但是，您不能在字段中混合数字和字符串。
回答by Nicholas
Try this:
尝试这个：
DECLARE C INTEGER;
SELECT COUNT(*) INTO C FROM tableMember WHERE Membergroup = 'testgroup';
IF C > 0
THEN
SELECT * FROM tableMember;
ELSE
SELECT 'No results!' FROM tableMember;
END IF;
