一个 sql查询 问题！

Question

select id,sum(decode(reason,'01',hours)) reason_1,sum(decode(reason,'02',hours)) reason_2,
sum(decode(reason,'03',hours))reason_3 from yk
group by id

Answer 1

如果 reason的数量不定的话 既要 使用动态sql 了 别的应该没有什么好方法了吧

Answer 2

SQL*PLus> desc emp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -----------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)SQL*PLus> select job, deptno, count(*)
  2       from emp
  3       group by job, deptno;JOB           DEPTNO   COUNT(*)
--------- ---------- ----------
CLERK             10          2
CLERK             20          4
CLERK             30          2
ANALYST           20          4
MANAGER           10          2
MANAGER           20          2
MANAGER           30          2
SALESMAN          30          8
PRESIDENT         10          2已选择9行。SQL*PLus> select job,
  2          max( decode( deptno, 10, cnt, null ) ) dept_10,
  3          max( decode( deptno, 20, cnt, null ) ) dept_20,
  4          max( decode( deptno, 30, cnt, null ) ) dept_30,
  5          max( decode( deptno, 40, cnt, null ) ) dept_40
  6        from ( select job, deptno, count(*) cnt
  7               from emp
  8               group by job, deptno )
  9      group by job
 10     /JOB          DEPT_10    DEPT_20    DEPT_30    DEPT_40
--------- ---------- ---------- ---------- ----------
ANALYST                       4
CLERK              2          4          2
MANAGER            2          2          2
PRESIDENT          2
SALESMAN                                 8-----------------------------------------------------------------------------------
各位，我有如下一个工资表，如：
   姓名  工资项   工资
   张三  基本工资  1000
   张三  岗位工资  2000
   张三  效益工资   200
   李四  基本工资  1000
   李四  效益工资  1000
   .......我需要用一个SQL语句得到如下所示的结果：
   姓名  基本工资  岗位工资  效益工资 .....
   张三  1000      2000       200     .....
   李四  1000         0      1000     .....
   ....
select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资, 
  sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, ....
from yourtable
group by 姓名

Answer 3

decode可以做到。。如果你的工资种类太多，可能要用过程去做了

Answer 4

我给你个例子看看吧：
create table stud
(
sid  varchar2(10),
kcbm  varchar2(10),
cj  int
);insert into stud values('1','语文',80);
insert into stud values('2','数学',90);
insert into stud values('3','英语',100);
commit;create or replace view cjd as
select sid,
decode(kcbm,'语文',cj,0) 语文,
decode(kcbm,'数学',cj,0) 数学,
decode(kcbm,'英语',cj,0) 英语
from stud
order by sid;select * from cjd;SID              语文       数学       英语                                     
---------- ---------- ---------- ----------                                     
1                  80          0          0                                     
2                   0         90          0                                     
3                   0          0        100