我是Oracle新手.我有一个包含三列的Oracle表：serialno,item_category和item_status.在第三列中,行具有serviceable,under_repair或condemned的值.
我想使用count来运行查询,以显示有多少是可维护的,有多少是在修复,有多少是针对每个项目类别被谴责的.
我想运行类似的东西：
select item_category,count(......) "total",count (.....) "serviceable",count(.....)"under_repair",count(....) "condemned" from my_table group by item_category ......
我无法在计数内运行内部查询.
这是我希望结果集看起来像：
item_category total serviceable under repair condemned ============= ===== ============ ============ =========== chair 18 10 5 3 table 12 6 3 3
解决方法
您可以在COUNT函数中使用CASE或DECODE语句.
SELECT item_category,COUNT (*) total,COUNT (DECODE (item_status,'serviceable',1)) AS serviceable,'under_repair',1)) AS under_repair,'condemned',1)) AS condemned
FROM mytable
GROUP BY item_category;
输出：
ITEM_CATEGORY TOTAL SERVICEABLE UNDER_REPAIR CONDEMNED ---------------------------------------------------------------- chair 5 1 2 2 table 5 3 1 1
