SQL 确定是否至少存在具有给定条件的一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14566910/
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
Identify if at least one row with given condition exists
提问by Kaushik Lele
Employee table has ID and NAME columns. Names can be repeated. I want to find out if there is at least one row with name like 'kaushik%'.
员工表有 ID 和 NAME 列。名称可以重复。我想知道是否至少有一行名称类似于“kaushik%”。
So query should return true/false or 1/0.
所以查询应该返回 true/false 或 1/0。
Is it possible to find it using single query. If we try something like
是否可以使用单个查询找到它。如果我们尝试类似
select count(1) from employee where name like 'kaushik%'
in this case it does not return true/false. Also we are iterating over all the records in table. Is there way in simple SQL such that whenever first record which satisfies condition is fetched, it should stop checking further records. Or such thing can only be handled in Pl/SQL block ?
在这种情况下，它不会返回真/假。此外，我们正在迭代表中的所有记录。在简单的 SQL 中有没有办法，只要满足条件的第一条记录被提取，它就应该停止检查进一步的记录。或者这样的事情只能在 Pl/SQL 块中处理？
EDIT *First approach provided by Justin looks correct answer
编辑 *贾斯汀提供的第一种方法看起来是正确的答案
SELECT COUNT(*) FROM employee WHERE name like 'kaushik%' AND rownum = 1
回答by Justin Cave
Commonly, you'd express this as either
通常，您可以将其表示为
SELECT COUNT(*)
FROM employee
WHERE name like 'kaushik%'
AND rownum = 1
where the rownum = 1predicate allows Oracle to stop looking as soon as it finds the first matching row or
其中rownum = 1谓词允许Oracle，就立即停止寻找，因为它找到的第一个匹配行或
SELECT 1
FROM dual
WHERE EXISTS( SELECT 1
FROM employee
WHERE name like 'kaushik%' )
where the EXISTSclause allows Oracle to stop looking as soon as it finds the first matching row.
whereEXISTS子句允许 Oracle 在找到第一个匹配行后立即停止查找。
The first approach is a bit more compact but, to my eye, the second approach is a bit more clear since you really are looking to determine whether a particular row exists rather than trying to count something. But the first approach is pretty easy to understand as well.
第一种方法更紧凑，但在我看来，第二种方法更清晰一些，因为您确实希望确定特定行是否存在，而不是试图计算某些内容。但是第一种方法也很容易理解。
回答by Gordon Linoff
How about:
怎么样：
select max(case when name like 'kraushik%' then 1 else 0 end)
from employee
Or, what might be more efficient since likecan use indexes:
或者，因为like可以使用索引，所以可能更有效：
select count(x)
from (select 1 as x
from employee
where name like 'kraushik%'
) t
where rownum = 1
回答by Max
since you require that the sql query should return 1 or 0, then you can try the following query :-
由于您要求 sql 查询应返回 1 或 0，因此您可以尝试以下查询：-
select count(1) from dual
where exists(SELECT 1
FROM employee
WHERE name like 'kaushik%')
Since the above query uses Exists, then it will scan the employee table and as soon as it encounters the first record where name matches "kaushik", it will return 1 (without scanning the rest of the table). If none of the records match, then it will return 0.
由于上述查询使用 Exists，因此它将扫描员工表，一旦遇到名称与“kaushik”匹配的第一条记录，它将返回 1（不扫描表的其余部分）。如果没有一条记录匹配，那么它将返回 0。
回答by user1974729
select 1
where exists ( select name
from employee
where name like 'kaushik%'
)
