从查询中获取结果 [英] fetch result from the query

问题描述

CREATE TABLE TEMP25
(
  STATUS  VARCHAR2(200 BYTE)
);

Insert into TEMP25
   (STATUS)
 Values
   ('PENDING');
Insert into TEMP25
   (STATUS)
 Values
   ('ERROR');
Insert into TEMP25
   (STATUS)
 Values
  ('ERROR');
Insert into TEMP25
   (STATUS)
 Values
   ('ERROR');
Insert into TEMP25
   (STATUS)
 Values
   ('NOT_REQUIRED');
Insert into TEMP25
   (STATUS)
 Values
   ('NOT_REQUIRED'); 
Insert into TEMP25
   (STATUS)
 Values
   ('PENDING');
Insert into TEMP25
   (STATUS)
 Values
   ('PENDING');
Insert into TEMP25
  (STATUS)
 Values
   ('ERROR');
Insert into TEMP25
   (STATUS)
Values
  ('NOT_REQUIRED');
Insert into TEMP25
  (STATUS)
 Values
  ('ERROR');
Insert into TEMP25
   (STATUS)
 Values
  ('INVALID');
Insert into TEMP25
   (STATUS) 
 Values
   ('INVALID');
Insert into TEMP25
  (STATUS)
 Values
  ('PENDING');

我想编写一个查询，使我得到类似

I want to write a query such that i get count like

TOTAL_RECORDS  TOTAL_PENDING TOTAL_NOT_REQUIRED   TOTAL_PENDING
-----------------------------------------------------------------
   14               4             3                   5 
-----------------------------------------------------------------

我想要按顺序进行记录，并且在一个查询中，我尝试使用with table子句来工作...是否还有其他解决方案...并且dnt想要无效的记录

I want the record in sequence and in one query , i ahve tried with with table clause it works ...is there any other solution ...and dnt want invalid records

推荐答案

您可以使用SUM/CASE或(如果愿意，可以使用SUM/DECODE)

You can use SUM/CASE or (SUM/DECODE if you prefer)

Select
   COUNT(*) TOTAL_RECORDS  ,
   SUM(case when status = 'PENDING' then 1 else 0 END) TOTAL_PENDING, 
   SUM(case when status = 'NOT_REQUIRED' then 1 else 0 END) TOTAL_NOT_REQUIRED, 
   SUM(case when status = 'ERROR' then 1 else 0 END) TOTAL_ERROR


FROM temp25

在此处查看其工作

您还可以使用数据透视，但是获取count(*)有点难看

You can also use pivot but getting the count(*) is a little ugly

WITH 
COUNTS AS(
select * 
from (
   select status
   from TEMP25 t
)
pivot 
(
   count(status)
   for status in ('PENDING' AS TOTAL_PENDING,
                  'NOT_REQUIRED' AS TOTAL_NOT_REQUIRED,
                  'ERROR' AS TOTAL_ERROR)
))


SELECT COUNT(*) total_records, 
       total_pending, 
       total_not_required, 
       total_error 
FROM   temp25, 
       counts 
GROUP  BY total_pending, 
          total_not_required, 
          total_error 

枢轴版本

这篇关于从查询中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！