SQL 如果表为空，如何返回 0，否则返回 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4138734/
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
How to return 0 if table empty, 1 otherwise
提问by Spawn
In postgreSQL, how can i return a table containing 0 if my table is empty and a table containing 1 if my table has rows?
在 postgreSQL 中，如果我的表为空，我如何返回一个包含 0 的表，如果我的表有行，我如何返回一个包含 1 的表？
I need to do it in SQL, not using any other language
我需要用 SQL 来做，而不是使用任何其他语言
回答by GendoIkari
Use:
用：
SELECT CASE
WHEN EXISTS (SELECT * FROM foo LIMIT 1) THEN 1
ELSE 0
END
EDIT: Added LIMIT 1 to speed up query.
编辑：添加 LIMIT 1 以加快查询速度。
回答by plundra
Might be a hack, but it works.
可能是一个黑客，但它的工作原理。
SELECT count(*) FROM (SELECT 1 FROM table LIMIT 1) AS t;
- The 1 selected in the sub-query could be whatever, it is just a place holder.
- The LIMIT 1 should make the sub-query very fast, regardless of table-size.
- 在子查询中选择的 1 可以是任何东西，它只是一个占位符。
- 无论表大小如何，LIMIT 1 都应该使子查询非常快。
Edit:Rewrote a bit. Previous use of LIMIT was wrong (didn't help on large tables as I intended).
编辑：重写了一点。以前使用 LIMIT 是错误的（对我预期的大表没有帮助）。
回答by Spawn
Try:
尝试：
select sign(count(*)) from mytable
回答by Julien Feniou
You can put this request in a stored procedure, with the table name as parameter :
您可以将此请求放入存储过程中，并将表名作为参数：
CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;
Then run this function like this :
然后像这样运行这个函数：
SELECT * FROM isEmpty('my_table_name');
So you can call it with any of your table's name
所以你可以用你的任何表名来调用它
回答by Randy
maybe this is what you are looking for?
也许这就是你要找的？
select min( c ) from (
select count(*) c
from mytab
union
select 1
from mytab
having count(*) > 1 )
回答by Randy
SELECT, COUNT and LIMIT should do it.
SELECT、COUNT 和 LIMIT 应该这样做。
-- see below SELECT COUNT(*) FROM X LIMIT 1
Edit:This doesn't work in Postgres(8.x at least). Please see the comments and here: http://postgresql.1045698.n5.nabble.com/window-function-count-and-limit-td3233588.html
编辑：这在 Postgres（至少 8.x）中不起作用。请参阅评论和此处：http: //postgresql.1045698.n5.nabble.com/window-function-count-and-limit-td3233588.html
Happy SQL'ing.
快乐的 SQL'ing。
回答by Tmas
I don't think that's possible using nothing but SQL. Why can't you use any other language?
我认为只使用 SQL 是不可能的。为什么你不能使用任何其他语言？
