Oracle SQL Query使用GROUP BY汇总统计信息 [英] Oracle SQL Query to Summarize Statistics, using GROUP BY

问题描述

我有一张Oracle数据表，其数据如下所示：

  ID批次状态
 1 1 0 
 2 1 0 
 3 1 1 
 4 2 0 
  

是， ID 是主键，每个批将有多行，并且每行在 STATUS 列中都有一个状态码。还有一些其他列，但这些是重要的列。

我需要编写一个查询来总结状态代码每个批次;在STATUS列中有三个可能的值，0,1和2，我希望看起来像这样的输出：

  BATCH STATUS0 STATUS1 STATUS2 
 1 2 1 0 
 2 1 0 0 
  

这些数字是计数;对于批次1，有

• 2条记录，其中 STATUS 设置为0
$ b $ STATUS 设置为1，并且

• 没有记录，其中 STATUS 设置为0。 li>
  

对于第二批，有

• 1记录 STATUS 设置为0，


• 没有记录，其中 STATUS 设置为1或2。 b $ b

有没有一种方法可以在一个查询中执行此操作，而无需为每个状态代码重写查询？即我可以很容易地写出这样的查询，然后运行它三次：

pre $ SELECT $ batch，COUNT（status）
FROM table
WHERE status = 0
GROUP BY批处理

我可以运行那么，然后再次运行它的状态= 1，并再次状态= 2，但我希望能够在一个查询中完成它。

如果它有所不同除了 STATUS 列之外，还有另一个列，我可能想以同样的方式总结 - 另一个原因是我不希望在执行SELECT语句之后SELECT语句并合并所有结果。

解决方案

  select batch 
，计数（状态= 1，然后1结束的情况）status1 
，计数（状态= 2，然后1结束的情况）status2 
，计数（状态= 3，然后1结束时的情况）status3 
从表
逐批; 
  

这通常称为数据透视查询，我写了一篇关于如何生成这些数据的文章在我的博客上动态查询 。

使用DECODE的版本（Oracle专用但不太详细）：

  select batch 
，count （decode（status，1,1））status1 
，count（decode（status，2,1））status2 
，count（decode（status，3,1））status3 
 from表
逐批; 
  

I have an Oracle table with data that looks like this:

ID   BATCH   STATUS
1    1       0
2    1       0
3    1       1
4    2       0

That is, ID is the primary key, there will be multiple rows for each "batch," and each row will have a status code in the STATUS column. There are a bunch of other columns, but these are the important ones.

I need to write a query which will summarize the status codes for each batch; there are three possible values that can go in the STATUS column, 0, 1, and 2, and I would like output that looks something like this:

BATCH  STATUS0  STATUS1  STATUS2
1      2        1        0
2      1        0        0

Those numbers would be counts; for batch 1, there are

• 2 records where STATUS is set to 0
• 1 record where STATUS is set to 1, and
• no records where STATUS is set to 0.

For batch 2, there is

• 1 record where STATUS is set to 0, and
• no records where STATUS is set to 1 or 2.

Is there a way that I can do this in one query, without having to rewrite the query for each status code? i.e. I can easily write a query like this, and run it three times:

SELECT batch, COUNT(status)
FROM table
WHERE status = 0
GROUP BY batch

I could run that, then run it again where status = 1, and again where status = 2, but I'm hoping to do it in one query.

If it makes a difference, aside from the STATUS column there is another column that I might want to summarize the same way--another reason that I don't want to have to execute SELECT statement after SELECT statement and amalgamate all of the results.

解决方案

select batch 
,      count(case when status=1 then 1 end) status1
,      count(case when status=2 then 1 end) status2
,      count(case when status=3 then 1 end) status3
from   table
group by batch;

This is often called a "pivot" query, and I have written an article about how to generate these queries dynamically on my blog.

Version using DECODE (Oracle-specific but less verbose):

select batch 
,      count(decode(status,1,1)) status1
,      count(decode(status,2,1)) status2
,      count(decode(status,3,1)) status3
from   table
group by batch;

这篇关于Oracle SQL Query使用GROUP BY汇总统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！