有表如下:
姓名 性别 职称 工资总额
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可否实现?谢谢
姓名 性别 职称 工资总额
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可否实现?谢谢
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);
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
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
数据原来的表里就有了,我就想从原来的表里生成一个试图应该怎样写呢?
谢谢!
--+上面的 sql 语句
是不是可以这样:
CREATE VIEW VIEW_NAME ASselect tt.name,
tt.sex,
tt.zicheng,
tt.money,
case when tt.sex = 'male'
then decode(tt.zicheng,'gaozhi',tt.money+100,'zhongcheng',tt.money+50,'chuzhi',tt.money+10,'chuchuzhi',tt.money+5)+100
when tt.sex = 'female'
then decode(tt.zicheng,'gaozhi',tt.money+50,'zhongcheng',tt.money+25,'chuzhi',tt.money+5,'chuchuzhi',tt.money+2.5)+50
end as change_money
from tt