带case条件语句的sum在sql中如何工作

How sum with case conditional statement works in sql(带case条件语句的sum在sql中如何工作)

本文介绍了带case条件语句的sum在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 和 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 语句.否则,赋值为0`.
  • 按部门汇总,将刚刚计算的值相加.这具有计算分析师数量的效果.
  • Look at each row in employees
  • If jobname is 'Analyst' then assign the value of 1 (this is the case statement. Otherwise, assign a value of0`.
  • 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.

这篇关于带case条件语句的sum在sql中如何工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:带case条件语句的sum在sql中如何工作

基础教程推荐