举个例子：计算字段一col1：

select count(case when col1='a' then 1 else 0 end) "a的个数和"

count(case when col1 is null then 1 else 0 end) "空的个数" 

.....

.....

from table_name

select 

count(DECODE((case to_char(sysdate,'D') WHEN 1 THEN COL7 

                                 WHEN 2 THEN COL1 

                                 WHEN 3 THEN COL2 

                                 WHEN 4 THEN COL3 

                                 WHEN 5 THEN COL4

                                 WHEN 6 THEN COL5 

                                 WHEN 7 THEN COL6 END),'a',1,null)),



count(NVL2((case to_char(sysdate,'D') WHEN 1 THEN COL7 

                                 WHEN 2 THEN COL1 

                                 WHEN 3 THEN COL2 

                                 WHEN 4 THEN COL3 

                                 WHEN 5 THEN COL4

                                 WHEN 6 THEN COL5 

                                 WHEN 7 THEN COL6 END),1,NULL))

from table











                                 

SQL> select * from t_chose;

 

COL1       COL2       COL3       COL4       COL5       COL6       COL7

---------- ---------- ---------- ---------- ---------- ---------- ----------

a          a                     a                     a          

           a          a                                a          

a                     a                     a                     

           a                     a                                a

 

SQL> 

SQL> select to_char(sysdate,'D'),

  2  count(DECODE((case to_char(sysdate,'D') WHEN '1' THEN COL7

  3                                   WHEN '2' THEN COL1

  4                                   WHEN '3' THEN COL2

  5                                   WHEN '4' THEN COL3

  6                                   WHEN '5' THEN COL4

  7                                   WHEN '6' THEN COL5

  8                                   WHEN '7' THEN COL6 END),'a',1,null)),

  9  count(NVL2((case to_char(sysdate,'D') WHEN '1' THEN COL7

 10                                   WHEN '2' THEN COL1

 11                                   WHEN '3' THEN COL2

 12                                   WHEN '4' THEN COL3

 13                                   WHEN '5' THEN COL4

 14                                   WHEN '6' THEN COL5

 15                                   WHEN '7' THEN COL6 END),NULL,1))

 16  from t_chose;

 

TO_CHAR(SYSDATE,'D') COUNT(DECODE((CASETO_CHAR(SYSD COUNT(NVL2((CASETO_CHAR(SYSDAT

-------------------- ------------------------------ ------------------------------

3                                                 3                              1

 

SQL> select to_char(sysdate,'dy') from dual;

 

TO_CHAR(SYSDATE,'DY')

---------------------

星期二

 

SQL>