在案例表达式中分配子查询的值

发布于 2025-01-23 02:16:39 字数 331 浏览 1 评论 0原文

我想获得一个值，如果它存在，如果它不存在，我想返回一个随机数（比这更复杂，但这就是它的本质）。

我认为我的查询看起来像这样：

SELECT 
    something, 
    CASE
        WHEN EXISTS (SELECT column_1 FROM table_1 WHERE something)  
            THEN column_1
            ELSE RANDOM()
    END;

这显然是无效的。

我使用PostgreSQL，并且在PLPGSQL函数中。

我应该怎么办？

原文

I want to get a value if it exists, if it doesn't exist I want to return a random number (it's a bit more complicated than that but that's the essence of it).

My query I think would look something like this:

SELECT 
    something, 
    CASE
        WHEN EXISTS (SELECT column_1 FROM table_1 WHERE something)  
            THEN column_1
            ELSE RANDOM()
    END;

which is not valid obviously.

I use Postgresql and I am inside a plpgsql function.

What should I do?

分享到QQ

分享到微博

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

发布评论

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

绻影浮沉 2025-01-30 02:16:39

我找到了！

使用 coalesce 我可以做到这一点

SELECT something, CΟALESCE((SELECT column_1 FROM table_1 WHERE something), RANDOM())

。，随机带头。

I found it!

using COALESCE I can do this:

SELECT something, CΟALESCE((SELECT column_1 FROM table_1 WHERE something), RANDOM())

When the first query does not return something, RANDOM takes the lead.

回复收藏 0 原文

尸血腥色 2025-01-30 02:16:39

您不能从存在条件中引用该列，因为这仅会产生True或false。从子查询中的选定列也完全忽略了。

因此，您需要在当时的零件中重复选择以获取列值：

SELECT 'something' as one_column, 
       CASE
         WHEN EXISTS (SELECT * 
                      FROM table_1 
                      WHERE <some_condition>) 
           THEN (select column_1 from table_1 where <some_condition>)
         ELSE random()
       END as other_column;

注意当时分支中选择周围的括号。它们是标记为标量查询所必需的。您还需要确保some_condition仅返回一行

You can't reference the column from an EXISTS condition as that only yields true or false. The SELECTed columns from the subquery are completely ignored as well.

So you need to repeat the SELECT in the THEN part to get the column value:

SELECT 'something' as one_column, 
       CASE
         WHEN EXISTS (SELECT * 
                      FROM table_1 
                      WHERE <some_condition>) 
           THEN (select column_1 from table_1 where <some_condition>)
         ELSE random()
       END as other_column;

Note the parentheses around the SELECT in the THEN branch. They are necessary to mark this as a scalar query. You also need to make sure that some_condition only returns a single row

回复收藏 0 原文

~没有更多了~