根据某些条件显示计数 [英] Show the count based on some condition

问题描述

几天前我问了一个问题。这是链接。
Count（）对应于max（）

I've asked a question some days back. Here is that link. Count() corresponding to max()

现在具有相同的表集（SQL小提琴）我想检查一个不同的条件

Now with the same set of tables (SQL Fiddle) I would like to check a different condition

如果第一个问题是关于与状态最大值相关的计数，此问题是关于根据每个项目的下一个状态显示计数。

If the first question was about a count related to the max of a status, this question is about showing the count based on the next status of every project.

说明

如表user_approval所示，appr_prjt_id = 1具有3个不同的状态，即10,20，30。下一个状态将是40（每批准一次，状态将增加10），依此类推。那么是否有可能表明某个项目的状态正在等待40？必须仅在输出中显示对应于40的状态的计数（而不是在状态10、20、30等）中显示

As you can see in the table user_approval,appr_prjt_id=1 has 3 different statuses namely 10,20 ,30. And the next status will be 40 (With every approval the status is increased by 10) and so on. So is it possible to show that there is a project whose status is waiting to be 40? Its count must only be shown for status corresponding to 40 in the output (not in the statuses 10,20,30,...etc)

所需的输出：

              10   |    20  |  30   |   40

location1     0    |    0   |   0   |   1

推荐答案

您可以获取下一个的计数状态查询中的状态要求类似于：

You can get the counts for each of the next status requirements with a query that looks more like:

select
    sum(case when ua.appr_status = 10 then 1 else 0 end) as app_waiting_20,
    sum(case when ua.appr_status = 20 then 1 else 0 end) as app_waiting_30,
    sum(case when ua.appr_status = 30 then 1 else 0 end) as app_waiting_40
from
    user_approval ua;

此解决方案的优点是仅一次表扫描，您可以添加所有其他计数/ sums也在查询结果中。

The nice thing about this solution is only one table scan, and you can add all kinds of other counts/sums in the query result as well.

这篇关于根据某些条件显示计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！