3,494
社区成员




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>
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
举个例子:计算字段一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