如何从 Ingres 的虚拟表中选择零或一条记录

发布于 2024-12-09 20:49:46 字数 371 浏览 0 评论 0原文

在大多数 SQL 产品中，我可以从无表或虚拟表中进行选择，如下所示：

-- Oracle
SELECT 1 FROM DUAL

-- Many other SQL products (including Ingres)
SELECT 1

有时，我想在上述语句中添加一个条件，以便根据条件检索 0 或 1 条记录

-- Oracle
SELECT 1 FROM DUAL WHERE 1 = 0

-- Many other SQL products (but not Ingres)
SELECT 1 WHERE 1 = 0

但上面的情况并没有适用于 Ingres 10.0。我该怎么做呢？

原文

In most SQL products, I can select from no table or from a dummy table like this:

-- Oracle
SELECT 1 FROM DUAL

-- Many other SQL products (including Ingres)
SELECT 1

Sometimes, I want to add a condition to the above statement, in order to retrieve 0 or 1 record, depending on the condition

-- Oracle
SELECT 1 FROM DUAL WHERE 1 = 0

-- Many other SQL products (but not Ingres)
SELECT 1 WHERE 1 = 0

But the above does not work for Ingres 10.0. How can I do it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

格子衫的從容 2024-12-16 20:49:46

我没有使用过 Ingres，但我从你的问题中假设，如果有 WHERE，则 FROM 是强制性的？怎么样

SELECT 1 FROM (SELECT 1 AS C) AS T WHERE 1 = 0

在这种情况下， Or

SELECT CASE WHEN 1 = 0 THEN 1 ELSE 0 END

（最后一个总是返回一行，但允许您检查条件）

I've not used Ingres but I assume from your question that a FROM is mandatory if there is a WHERE? In that case how about

SELECT 1 FROM (SELECT 1 AS C) AS T WHERE 1 = 0

SELECT CASE WHEN 1 = 0 THEN 1 ELSE 0 END

(The last one will always return a row but allow you to check a condition)

回复收藏 0 原文

帅气尐潴 2024-12-16 20:49:46

据我所知，系统目录 iidbconstants 只有一行，因此您可以使用它。我无法想到有多行的情况，但您可能需要添加 DISTINCT 以防万一：

select distinct 1 from iidbconstants
Executing . . .


+------+
|col1  |
+------+
|     1|
+------+
(1 row)
continue
* select distinct 1 from iidbconstants where 1 = 0
Executing . . .


+------+
|col1  |
+------+
+------+
(0 rows)

AFAIK the system catalog iidbconstants only has one row, so you could use that. I can't think of a case where there are multiple rows but you may want to add a DISTINCT in case:

select distinct 1 from iidbconstants
Executing . . .


+------+
|col1  |
+------+
|     1|
+------+
(1 row)
continue
* select distinct 1 from iidbconstants where 1 = 0
Executing . . .


+------+
|col1  |
+------+
+------+
(0 rows)

回复收藏 0 原文

~没有更多了~