带有条件条件语句的总和在SQL中如何工作 [英] How sum with case conditional statement works in sql
问题描述
前几天,我回答了这个问题,但是其他用户使用sum + case条件语句解决了该问题,并在结果中添加了一个边缘条件.因此,我想到了一个问题,以下查询中的sum(case when jobname = 'Analyst' then 1 else 0 end)
语句是如何工作的
The other day, I gave an answer to this question but then other user solved that problem with sum + case conditional statement to add one edge condition in result. So, the question came to my mind, how statement sum(case when jobname = 'Analyst' then 1 else 0 end)
in the below query works
select d.*
from (select deptno,
sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
from employees
group by deptno
order by numAnalysts asc
) d
where rownum = 1;`
,并返回部门中的员工人数.另外,我想了解此查询的性能.
and return the number of employees over a department. Also, I would like to understand the performance of this query.
在发布此问题之前,我阅读了 this , this 和此,但仍然没有无法了解其工作原理.
Before posting this question, I read this, this and this but still didn't get how this works.
推荐答案
大概这是您在努力理解的部分:
Presumably, this is the part that you are struggling to understand:
select deptno,
sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
from employees
group by deptno
这确实是一个简单的聚合查询.该查询正在执行的操作是:
This is a simple aggregation query, really. What the query is doing is:
- 查看
employees
中的每一行
- 如果
jobname
是'Analyst'
,则分配1
的值(这是case
语句. Otherwise, assign a value of
0`. - 按部门汇总,将刚刚计算出的值相加.这具有计算分析师人数的作用.
- Look at each row in
employees
- If
jobname
is'Analyst'
then assign the value of1
(this is thecase
statement. Otherwise, assign a value of
0`. - Aggregate by department, summing the value just calculated. This has the effect of counting the number of analysts.
case
是一个返回值的表达式. sum()
只是将每个组的值相加.
case
is an expression that returns a value. The sum()
is simply adding up that value for each group.
这篇关于带有条件条件语句的总和在SQL中如何工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!