用PL/SQL语言，不能使用GROUP BY 和连接。

Question

10**.[使用table:EMP]
    依SAL列出各部门在各区间的人数
    使用N个DECODE    DEPTNO $3001-$9999 $2001-$3000 $1001-$2000    < $1000
---------- ----------- ----------- ----------- ----------
        10           1           1           1          0
        20           0           3           1          1
        30           0           1           4          1

Answer 1

SQL> select * from tbl;                                  EMPNO ENAME                                                                        SAL                                  DEPTNO
--------------------------------------- ---------------------------------------- --------------------------------------- ---------------------------------------
                                   7788 scott                                                                       3000                                      20
                                   7902 ford                                                                        3000                                      20
                                   7521 ward                                                                        1250                                      20
                                   7654 martin                                                                      1250                                      20
                                   7789 scott1                                                                      2000                                      20
                                   7790 scott1                                                                       800                                      30
                                   7791 scott1                                                                      1200                                      40
                                   7792 scott1                                                                      2200                                      10
                                   7793 scott1                                                                      3200                                      30
                                   7794 scott1                                                                      4200                                      1010 rows selectedSQL> 
SQL> select deptno,
  2  sum(case when sal<1000 then 1 else 0 end) "<1000",
  3  sum(case when sal>1000 and sal<2000 then 1 else 0 end) "1001-2000",
  4  sum(case when sal>2000 and sal<3000 then 1 else 0 end) "2001-3000",
  5  sum(case when sal>3000 and sal<9999 then 1 else 0 end) "3001-9999"
  6  from tbl group by deptno order by deptno;                                 DEPTNO      <1000  1001-2000  2001-3000  3001-9999
--------------------------------------- ---------- ---------- ---------- ----------
                                     10          0          0          1          1
                                     20          0          2          0          0
                                     30          1          0          0          1
                                     40          0          1          0          0SQL> 

Answer 2

select deptno,
   sum(case when sal>3000 and sal<9999 then 1 else 0 end) "$3001-$9999",
   sum(case when sal>2000 and sal<3000 then 1 else 0 end) "$2001-$3000",
   sum(case when sal>1000 and sal<2000 then 1 else 0 end) "$1001-$2000",
   sum(case when sal<1000 then 1 else 0 end) "<$1000"
  from emp group by deptno order by deptno;