求一条sql,请帮看看!
有表如下:
姓名 性别 职称 工资总额
a 男 高职 3000
b 男 中职 2000
c 女 初职 1500
d 女 初初职 900
我想(通过一条sql)得到如下视图;
姓名 性别 职称 工资总额 变动后的工资
a 男 高职 3050 3250
e 男 高职 3000 3200
b 男 中职 2000 2150
c 女 初职 1500 2060
d 女 初初职 900 955
条件是这样的
男性(+100),女性(+50)
高职(+100),中职(+50),初职(+10),初初职(+5);
不知用一条sql可否实现?谢谢
[解决办法]
CREATE TABLE TABLE1(NAME VARCHAR2(10),SEX VARCHAR2(4),JOB VARCHAR2(10),SAL INTEGER);
INSERT INTO TABLE1 VALUES( 'a ', '男 ', '高职 ',3000);
INSERT INTO TABLE1 VALUES( 'b ', '男 ', '中职 ',2000);
INSERT INTO TABLE1 VALUES( 'c ', '女 ', '初职 ',1500);
INSERT INTO TABLE1 VALUES( 'd ', '女 ', '初初职 ',900);
COMMIT;
SELECT NAME,SEX,JOB,SAL,
DECODE(JOB, '高职 ',SAL_NEW+100, '中职 ',SAL_NEW+50, '初职 ',SAL_NEW+10, '初初职 ',SAL_NEW+5)SAL_NEW FROM
(SELECT NAME,SEX,JOB,SAL,DECODE(SEX, '男 ',SAL+100,SAL+50) SAL_NEW FROM TABLE1);
NAME SEX JOB SAL SAL_NEW
---------- ---- ---------- ---------- ----------
a 男 高职 3000 3200
b 男 中职 2000 2150
c 女 初职 1500 1560
d 女 初初职 900 955
创建试图的话就
CREATE VIEW VIEW_NAME AS
SELECT NAME,SEX,JOB,SAL,
DECODE(JOB, '高职 ',SAL_NEW+100, '中职 ',SAL_NEW+50, '初职 ',SAL_NEW+10, '初初职 ',SAL_NEW+5)SAL_NEW FROM
(SELECT NAME,SEX,JOB,SAL,DECODE(SEX, '男 ',SAL+100,SAL+50) SAL_NEW FROM TABLE1);
[解决办法]
SQL> select tt.name,tt.sex,tt.zicheng,tt.money,
2 case when tt.zicheng = 'gaozhi ' then decode(tt.sex, 'male ',tt.money+100, 'female ',tt.money+50)+100
3 when tt.zicheng = 'zhongcheng ' then decode(tt.sex, 'male ',tt.money+100, 'female ',tt.money+50)+50
4 when tt.zicheng = 'chuzhi ' then decode(tt.sex, 'male ',tt.money+100, 'female ',tt.money+50)+10
5 when tt.zicheng = 'chuchuzhi ' then decode(tt.sex, 'male ',tt.money+100, 'female ',tt.money+50)+5
6 end as change_money
7 from (select 'a ' as name, 'male ' as sex, 'gaozhi ' as zicheng,3000 as money from dual
8 union all
9 select 'b ' as name, 'male ' as sex, 'zhongcheng ' as zicheng,2000 as money from dual
10 union all
11 select 'c ' as name, 'female ' as sex, 'chuzhi ' as zicheng,1500 as money from dual
12 union all
13 select 'd ' as name, 'female ' as sex, 'chuchuzhi ' as zicheng,900 as money from dual
14 )tt;
NAME SEX ZICHENG MONEY CHANGE_MONEY
---- ------ ---------- ---------- ------------
a male gaozhi 3000 3200
b male zhongcheng 2000 2150
c female chuzhi 1500 1560
d female chuchuzhi 900 955
[解决办法]
学习了一下:)
[解决办法]
SQL> select tt.name,
2 tt.sex,
3 tt.zicheng,
4 tt.money,
5 case when tt.sex = 'male '
6 then decode(tt.zicheng, 'gaozhi ',tt.money+100, 'zhongcheng ',tt.money+50, 'chuzhi ',tt.money+10, 'chuchuzhi ',tt.money+5)+100
7 when tt.sex = 'female '
8 then decode(tt.zicheng, 'gaozhi ',tt.money+50, 'zhongcheng ',tt.money+25, 'chuzhi ',tt.money+5, 'chuchuzhi ',tt.money+2.5)+50
9 end as change_money
10 from (select 'a ' as name, 'male ' as sex, 'gaozhi ' as zicheng,3000 as money from dual
11 union all
12 select 'b ' as name, 'male ' as sex, 'zhongcheng ' as zicheng,2000 as money from dual
13 union all
14 select 'c ' as name, 'female ' as sex, 'chuzhi ' as zicheng,1500 as money from dual
15 union all
16 select 'd ' as name, 'female ' as sex, 'chuchuzhi ' as zicheng,900 as money from dual
17 )tt;
NAME SEX ZICHENG MONEY CHANGE_MONEY
---- ------ ---------- ---------- ------------
a male gaozhi 3000 3200
b male zhongcheng 2000 2150
c female chuzhi 1500 1555
d female chuchuzhi 900 952.5
[解决办法]
CREATE VIEW VIEW_NAME AS
--+上面的 sql 语句
[解决办法]