检查条件的输出并返回一个值。

huangapple go评论59阅读模式
英文:

Checking the output for a condition and returning a value

问题

以下查询从两个表中获取计数并将它们相除以得到一个数字(我在此处使用交叉连接)。我想要检查结果是否大于零。如果大于零,则输出自定义消息,否则返回null。我该如何实现这个目标?

select 
    case 
        when r.cnt / o.cnt > 0 then '自定义消息'
        else null
    end
from (select count(*) from table_A) r
cross join
(select count(*) from table_B) o;
英文:

the below query takes the count from two tables and divides them to get a number (I am using a cross join for this).

I want to check if the result is greater than zero. If its greater than zero, then output a custom message, else return null. How can i achieve this?

select r.cnt / o.cnt
from (select count(*) from table_A) r
cross join
(select count(*) from table_B) o;

答案1

得分: 2

你可以使用CASE语句来检查除法的结果是否大于零,然后根据情况输出自定义消息或返回null。

SELECT 
  CASE
    WHEN r.cnt / o.cnt > 0 THEN '自定义消息'
    ELSE NULL
  END
FROM (SELECT COUNT(*) FROM table_A) r
CROSS JOIN
(SELECT COUNT(*) FROM table_B) o;

此查询检查条件 r.cnt / o.cnt > 0,如果为真,则输出'自定义消息';否则返回NULL。

英文:

You can use a CASE statement to check if the result of the division is greater than zero and output a custom message or return null accordingly.

SELECT 
  CASE
    WHEN r.cnt / o.cnt > 0 THEN 'Custom Message'
    ELSE NULL
  END
FROM (SELECT COUNT(*) FROM table_A) r
CROSS JOIN
(SELECT COUNT(*) FROM table_B) o;

This query checks the condition r.cnt / o.cnt > 0 and, if true, outputs 'Custom Message'; otherwise, it returns NULL.

答案2

得分: 2

你可以尝试这个查询:

选择 CASE
当(选择 COUNT(*) FROM table_A )/(选择 COUNT(*) FROM table_B )> 0 THEN '自定义消息'
否则 NULL END FROM dual;
英文:

You can try this Query :

SELECT  CASE
WHEN (SELECT COUNT(*) FROM table_A ) / (SELECT COUNT(*) FROM table_B ) > 0 THEN 'Custom Message'
ELSE NULL END FROM dual;

答案3

得分: 1

用一个变量更容易实现:

设置 ratio =(从表_A 中选择 count(*))/(从表_B 中选择 count(*));

选择 case when $ratio > 0 then '自定义消息' end as out_message
英文:

Easier done with a variable..

set ratio=(select count(*) from table_A) / (select count(*) from table_B);

select case when $ratio>0 then 'custom message' end as out_message

huangapple
  • 本文由 发表于 2023年5月11日 12:14:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76224110.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定