oracle 同一列上的不同值计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18297185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Different value counts on same column
提问by raja
I am new to Oracle. I have an Oracle table with three columns: serialno, item_categoryand item_status. In the third column the rows have values of serviceable, under_repairor condemned.
我是 Oracle 的新手。我有一个包含三列的 Oracle 表：serialno,item_category和item_status. 在第三列中，行的值为serviceable,under_repair或 condemned。
I want to run the query using count to show how many are serviceable, how many are under repair, how many are condemned against each item category.
我想使用 count 运行查询以显示有多少可以维修，有多少正在维修，有多少是针对每个项目类别的。
I would like to run something like:
我想运行类似的东西：
select item_category
, count(......) "total"
, count (.....) "serviceable"
, count(.....)"under_repair"
, count(....) "condemned"
from my_table
group by item_category ......
I am unable to run the inner query inside the count.
我无法在计数内运行内部查询。
Here's what I'd like the result set to look like:
这是我希望结果集的样子：
item_category total serviceable under repair condemned
============= ===== ============ ============ ===========
chair 18 10 5 3
table 12 6 3 3
回答by Noel
You can either use CASE or DECODE statement inside the COUNT function.
您可以在 COUNT 函数内使用 CASE 或 DECODE 语句。
SELECT item_category,
COUNT (*) total,
COUNT (DECODE (item_status, 'serviceable', 1)) AS serviceable,
COUNT (DECODE (item_status, 'under_repair', 1)) AS under_repair,
COUNT (DECODE (item_status, 'condemned', 1)) AS condemned
FROM mytable
GROUP BY item_category;
Output:
输出：
ITEM_CATEGORY TOTAL SERVICEABLE UNDER_REPAIR CONDEMNED
----------------------------------------------------------------
chair 5 1 2 2
table 5 3 1 1
回答by Burhan Ali
This is a very basic "group by" query. If you search for that you will find plentyofdocumentationon how it is used.
这是一个非常基本的“分组依据”查询。如果您搜索它，您会发现大量有关如何使用它的文档。
For your specific case, you want:
对于您的具体情况，您需要：
select item_category, item_status, count(*)
from <your table>
group by item_category, item_status;
You'll get something like this:
你会得到这样的东西：
item_category item_status count(*)
======================================
Chair under_repair 7
Chair condemned 16
Table under_repair 3
Change the column ordering as needed for your purpose
根据需要更改列顺序
回答by oraclenerd
I have a tendency of writing this stuffup so when I forget how to do it, I have an easy to find example.
我有写这些东西的倾向，所以当我忘记怎么做时，我有一个很容易找到的例子。
The PIVOT clause was new in 11g. Since that was 5+ years ago, I'm hoping you are using it.
PIVOT 子句是 11g 中的新增内容。因为那是 5 多年前的事了，我希望你正在使用它。
Sample Data
样本数据
create table t
(
serialno number(2,0),
item_category varchar2(30),
item_status varchar2(20)
);
insert into t ( serialno, item_category, item_status )
select
rownum serialno,
( case
when rownum <= 12 then 'table'
else 'chair'
end ) item_category,
( case
--table status
when rownum <= 12
and rownum <= 6
then 'servicable'
when rownum <= 12
and rownum between 7 and 9
then 'under_repair'
when rownum <= 12
and rownum > 9
then 'condemned'
--chair status
when rownum > 12
and rownum < 13 + 10
then 'servicable'
when rownum > 12
and rownum between 23 and 27
then 'under_repair'
when rownum > 12
and rownum > 27
then 'condemned'
end ) item_status
from
dual connect by level <= 30;
commit;
and the PIVOT query:
和 PIVOT 查询：
select *
from
(
select
item_status stat,
item_category,
item_status
from t
)
pivot
(
count( item_status )
for stat in ( 'servicable' as "servicable", 'under_repair' as "under_repair", 'condemned' as "condemned" )
);
ITEM_CATEGORY servicable under_repair condemned
------------- ---------- ------------ ----------
chair 10 5 3
table 6 3 3
I still prefer @Ramblin' Man's way of doing it (except using CASE in place of DECODE) though.
不过，我仍然更喜欢@Ramblin' Man 的做法（除了使用 CASE 代替 DECODE）。
Edit
编辑
Just realized I left out the TOTAL column. I'm not sure there's a way to get that column using the PIVOT clause, perhaps someone else knows how. May also be the reason I don't use it that often.
刚刚意识到我遗漏了 TOTAL 列。我不确定有没有办法使用 PIVOT 子句获取该列，也许其他人知道如何。也可能是我不经常使用它的原因。
