请教一个SQL的写法

Question

select a.classcode,b.classname,count(a.claacode)  as Number 
from (select decode(classcode,'05','04','06','04',classcode) classcode,classname from 表a) a inner join 表b b on a.classcode=b.classcode
group by a.classcode,b.classname having count(a.classcode)>=3

Answer 1

是这样？
select decode(a.classcode,'05','04','06,'04',a.classcode) code1,b.classname,count(a.claacode)  as Number from 表a a inner join 表b b on a.classcode=b.classcode
group by decode(a.classcode,'05','04','06,'04',a.classcode),b.classname having count(a.classcode)>=3

Answer 2

select * from (
select a.classcode,b.classname,count(a.claacode) over(partition by decode(a.classcode,'05','04','06,'04',a.classcode)) as num 
from 表a a inner join 表b b on a.classcode=b.classcode
) where num>= 3

Answer 3

SQL> select * from test;        ID CLASSCODE
---------- ----------
         1 01
         2 01
         3 01
         4 01
         5 02
         6 02
         7 02
         8 03
         9 03
        10 03
        11 04
        12 05
        13 05
        14 06
        15 06
        16 06已选择16行。SQL> select * from test1;CLASSCODE  CLASSNAME
---------- ----------
01         aa
02         bb
03         cc
04         dd
05         ee
06         ff已选择6行。SQL> select a.classcode,b.classname,count(a.classcode) as "Number" from
  2  (select decode(classcode,'05','04','06','04',classcode) classcode from test) a,
  3  test1 b
  4  where a.classcode=b.classcode
  5  group by a.classcode,b.classname having count(a.classcode)>=3;CLASSCODE  CLASSNAME      Number
---------- ---------- ----------
01         aa                  4
02         bb                  3
03         cc                  3
04         dd                  6SQL>

Answer 4

楼主的意思可能是这样的吧，他应该想把04，05，06归于一类计算count，最后结果分开来表示：
CLASSCODE  CLASSNAME      Number
---------- ---------- ----------
01         aa                  4
02         bb                  3
03         cc                  3
04         dd                  1     或者是04，05，06的总数6
05         dd                  2
06         dd                  3

Answer 5

select decode(classcode,'05','04','06','04',classcode) classcode,classname,num,class_num from (
select distinct a.classcode,b.classname,
       count(a.classcode) over(partition by a.classcode) num,
       count(a.classcode) over(partition by decode(a.classcode,'05','04','06','04',a.classcode)) as class_num 
from ta a inner join tb b on a.classcode=b.classcode
) where class_num>= 3SQL> /CLASSCODE  CLASSNAME                   NUM  CLASS_NUM
---------- -------------------- ---------- ----------
01         木头                          4          4
02         钢铁                          3          3
03         水藻                          3          3
04         苹果                          1          6
04         香蕉                          2          6
04         西瓜                          3          66 rows selected

Answer 6

首先感谢楼上各位的帮助，
bzszp(SongZip) 的解答是正确的，除了几个小的语法错误
duanzilin(寻) 的解答是错误的，因为没有Group by，还有可能理解错误了
waterfirer(水清) 的解答也是正确的，但与bzszp(SongZip)其实是一致的再次感谢