求教一个筛选考核结果的问题。

[songwin]

考核表
ID col1 col2 col3 col4
1 A B C D
2 C D C D
3 A A C A
4 D A A A
5 B B A C

col1、col2、col3、col4分别是员工的四次考核结果（每次考核结果可能是ABCD中的某一个），需要筛选四次考核中考核结果C、D次数在两次以下的人员ID。请问该如何做出来？

[lbd8848]

select id from table where exists (select count(*) from table where col1='C' or col2='C' ... or col1='D' or col2='D')<2

[lbd8848]

改一下:
select id from table a where exists (select count(*) from table where (col1='C' or col2='C' ... or col1='D' or col2='D') and a.id=id)<2

[xiaoxiao1984]

SQL> select id, col1,col2,col3,col4
2 from test_assess
3 where length(col1||col2||col3||col4) - nvl(length(replace(replace(col1||col2||col3||col4,'C',''),'D','')),0) >= 2
4 /

ID COL1 COL2 COL3 COL4
---------- ---- ---- ---- ----
1 A B C D
2 C D C D

[xiaoxiao1984]

SQL> select id, col1,col2,col3,col4
2 from test_assess
3 where length(col1||col2||col3||col4) - nvl(length(replace(replace(col1||col2||col3||col4,'C',''),'D','')),0) < 2
4 /

ID COL1 COL2 COL3 COL4
---------- ---- ---- ---- ----
3 A A C A
4 D A A A
5 B B A C

[Eric_1999]

select id
from tab
where length(col1||col2||col3||col4) - nvl(length(replace(replace(col1||col2||col3||col4,'C',''),'D','')),0) < 2