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

How sum with case conditional statement works in sql

提问by codiacTushki

The other day, I gave an answer to thisquestion 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

前几天，我回答了这个问题，但后来其他用户用 sum + case 条件语句解决了这个问题，在结果中添加了一个边缘条件。所以，我想到了这个问题，sum(case when jobname = 'Analyst' then 1 else 0 end)下面查询中的语句是如何工作的

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.

并返回一个部门的员工人数。另外，我想了解此查询的性能。

Before posting this question, I read this, thisand thisbut still didn't get how this works.

在发布这个问题之前，我阅读了这个，这个和这个，但仍然没有理解它是如何工作的。

回答by Gordon Linoff

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:

这是一个简单的聚合查询，真的。查询正在做的是：

• Look at each row in employees
• If jobnameis 'Analyst'then assign the value of 1(this is the casestatement. Otherwise, assign a value of0`.
• Aggregate by department, summing the value just calculated. This has the effect of counting the number of analysts.

• 查看中的每一行 employees
• 如果jobname是'Analyst'然后分配值1（这是case语句. Otherwise, assign a value of0`。
• 按部门汇总，将刚刚计算的值相加。这具有计算分析师数量的效果。

caseis an expression that returns a value. The sum()is simply adding up that value for each group.

case是一个返回值的表达式。这sum()只是将每个组的值相加。

回答by CrApHeR

Lets try to split the problem in 2 parts.

让我们尝试将问题分成两部分。

First, let suppose you want a field saying if the jobname is 'Analyst' or not.

首先，假设您想要一个字段，说明工作名称是否为“分析师”。

SELECT
  deptno,
  CASE WHEN jobname = 'Analyst' THEN 1 ELSE 0 END AS IsAnalyst
FROM
  employees

This query is going to return 0 for all the jobname that are no 'Analyst' and 1 for all the ones that are 'Analyst'

此查询将为所有非“分析员”的作业名返回 0，为所有“分析员”返回 1

At this point you will have something like this

此时你会有这样的事情

deptno IsAnalyst
1      1
1      0
1      0
2      0
2      1
2      1
2      1
2      0

Second, You want to summarize this information by department

其次，您要按部门汇总此信息

SELECT
  deptno,
  SUM(CASE WHEN jobname = 'Analyst' THEN 1 ELSE 0 END) AS numAnalysts
FROM
  employees
GROUP BY
  deptno

You are applying a sum to all those value and grouping by deptno.

您正在对所有这些值应用总和并按 deptno 分组。

At this point (I removed the order byfrom the query to simplify it) you will have the following output

此时（我order by从查询中删除了以简化它）您将获得以下输出

deptno numAnalysts
1      1
2      3

I think that an example is worth a thousand words

我认为一个例子值一千字

Hope this helps

希望这可以帮助