使用 GROUP BY 汇总统计信息的 Oracle SQL 查询

2022-01-22 00:00:00 pivot sql group-by oracle

我有一个 Oracle 表，其中的数据如下所示:

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

即ID为主键，每个批次"会有多行，每一行在STATUS列都有一个状态码.还有很多其他列，但这些是重要的.

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.

我需要编写一个查询来总结每个批次的状态代码；STATUS 列中可以包含三个可能的值，0、1 和 2，我希望输出看起来像这样:

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

这些数字将是计数；对于第 1 批，有

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

  • 2 条记录，其中 STATUS 设置为 0
  • 1 条记录，其中 STATUS 设置为 1，并且
  • 没有 STATUS 设置为 0 的记录.
  • 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.

对于第 2 批，有

  • 1 条记录，其中 STATUS 设置为 0，并且
  • 没有将 STATUS 设置为 1 或 2 的记录.
  • 1 record where STATUS is set to 0, and
  • no records where STATUS is set to 1 or 2.

有没有一种方法可以在一个查询中执行此操作，而无需为每个状态代码重写查询?即我可以轻松编写这样的查询，然后运行 ​​3 次:

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

我可以运行它，然后在 status = 1 的地方再次运行它，然后在 status = 2 的地方再次运行它，但我希望在一个查询中完成.

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.

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

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;

这通常被称为pivot"查询，我写了一篇关于如何动态生成这些查询的文章在我的博客上.

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

使用 DECODE 的版本(特定于 Oracle，但不太冗长):

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;

相关文章