英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论