。。 那现在问了别人也没法回答啊有两种方式, 1.case when 2.where 后面的条件使用orselect case when 硕士 and 工资 > 800 then 工资 when 博士 and (2009年前 and 工资 > 1200 or 2009年后 and 工资 > 1500) then 工资 end from table;select * from table where 硕士 and 工资 > 800 or 博士 and (2009年前 and 工资 > 1200 or 2009年后 and 工资 > 1500);
select * from (xw = '硕士' and gz >= 800) or (xw = '博士' and ((gz >= 1200 and to_char(dt,'yyyy') < '2009') or (gz >= 1500 and to_char(dt,'yyyy') >= '2009')) )
select *from table_name where (xw='硕士' and salary>800) or (xw='博士' and to_char(rssj,'yyyy')<=2009 and salary>1200) or (xw='博士' and to_char(rssj,'yyyy')< 2009 and salary>1500);
如果是条件的话,用or的方式就可以了呀where (title='硕士' and salary > 800) or (title='博士' and ((joindate<'2009' and salary>1200) or joindate>'2009' and salary>1500))
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t6 on t6.sfid = t1.sfid
inner join hust_xx_profile t7 on t7.uuid = t6.uuid where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t2.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t2.rxny,1,4)>2009) and t1.ffny=200910 order by t1.ffbz asc 其中 sxlbm='21' 代表硕士 sxlbm='25' 代表博士 rxmy是年份。200910是数据库取得的字符串substr(t2.rxny,1,4)取得2009 可是oracle运行不了
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t6 on t6.sfid = t1.sfid
inner join hust_xx_profile t7 on t7.uuid = t6.uuid where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t4.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t4.rxny,1,4)>2009) and t1.ffny=200910 order by t1.ffbz asc 其中 sxlbm='21' 代表硕士 sxlbm='25' 代表博士 rxmy是年份。200910是数据库取得的字符串substr(t2.rxny,1,4)取得2009 oracle成功了,但是没有测试,大家帮我看看有没有问题
select t1.sfid , t1.ffbh , t1.ffbz , t1.gwdj , t1.sjffjt , t2.gwmc , t2.gwlx , t2.jhjt , t4.xh , t4.sxlbm , t4.xjzt , t4.rxny , t4.ywszzg , t5.nj , t6.dwmc , t7.xm from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh inner join hust_xx_department t6 on t6.DWBH = t5.DWBH inner join hust_xx_identity t6 on t6.sfid = t1.sfid inner join hust_xx_profile t7 on t7.uuid = t6.uuid where (t4.sxlbm = '21' and t1.sjffjt > 800) or (t4.sxlbm = '25' and t1.sjffjt > 1200 and substr(t4.rxny,1,4) < 2009) or (t4.sxlbm = '25' and t1.sjffjt > 1500 and substr(t4.rxny,1,4) > 2009) and t1.ffny = 200910 order by t1.ffbz;格式化了以下,发现代码是错的 里面有两个t6 hust,我们可是同城学校哦。
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t7 on t7.sfid = t1.sfid
inner join hust_xx_profile t8 on t8.uuid = t7.uuid where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t4.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t4.rxny,1,4)>2009) and t1.ffny=#ffny# order by t1.ffbz asc
那现在问了别人也没法回答啊有两种方式,
1.case when
2.where 后面的条件使用orselect case when 硕士 and 工资 > 800 then 工资 when 博士 and (2009年前 and 工资 > 1200 or
2009年后 and 工资 > 1500) then 工资 end from table;select * from table where 硕士 and 工资 > 800 or 博士 and (2009年前 and 工资 > 1200 or
2009年后 and 工资 > 1500);
where (xw='硕士' and salary>800)
or (xw='博士' and to_char(rssj,'yyyy')<=2009 and salary>1200)
or (xw='博士' and to_char(rssj,'yyyy')< 2009 and salary>1500);
如果是条件的话,用or的方式就可以了呀where (title='硕士' and salary > 800) or (title='博士' and ((joindate<'2009' and salary>1200) or joindate>'2009' and salary>1500))
select t1.sfid,t1.ffbh, t1.ffbz,t1.gwdj,t1.sjffjt,
t2.gwmc,t2.gwlx, t2.jhjt, t4.xh, t4.sxlbm,
t4.xjzt,t4.rxny,
t4.ywszzg,
t5.nj,
t6.dwmc,
t7.xm
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh
inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t6 on t6.sfid = t1.sfid
inner join hust_xx_profile t7 on t7.uuid = t6.uuid
where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t2.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t2.rxny,1,4)>2009)
and t1.ffny=200910
order by t1.ffbz asc
其中 sxlbm='21' 代表硕士
sxlbm='25' 代表博士
rxmy是年份。200910是数据库取得的字符串substr(t2.rxny,1,4)取得2009
可是oracle运行不了
select t1.sfid,t1.ffbh, t1.ffbz,t1.gwdj,t1.sjffjt,
t2.gwmc,t2.gwlx, t2.jhjt, t4.xh, t4.sxlbm,
t4.xjzt,t4.rxny,
t4.ywszzg,
t5.nj,
t6.dwmc,
t7.xm
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh
inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t6 on t6.sfid = t1.sfid
inner join hust_xx_profile t7 on t7.uuid = t6.uuid
where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t4.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t4.rxny,1,4)>2009)
and t1.ffny=200910
order by t1.ffbz asc
其中 sxlbm='21' 代表硕士
sxlbm='25' 代表博士
rxmy是年份。200910是数据库取得的字符串substr(t2.rxny,1,4)取得2009
oracle成功了,但是没有测试,大家帮我看看有没有问题
, t1.ffbh
, t1.ffbz
, t1.gwdj
, t1.sjffjt
, t2.gwmc
, t2.gwlx
, t2.jhjt
, t4.xh
, t4.sxlbm
, t4.xjzt
, t4.rxny
, t4.ywszzg
, t5.nj
, t6.dwmc
, t7.xm
from hust_sam_ast_monthdeliver t1
inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid
inner join hust_xx_class t5 on t5.bjbh = t4.bjbh
inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t6 on t6.sfid = t1.sfid
inner join hust_xx_profile t7 on t7.uuid = t6.uuid
where (t4.sxlbm = '21' and t1.sjffjt > 800)
or (t4.sxlbm = '25' and t1.sjffjt > 1200 and substr(t4.rxny,1,4) < 2009)
or (t4.sxlbm = '25' and t1.sjffjt > 1500 and substr(t4.rxny,1,4) > 2009)
and t1.ffny = 200910
order by t1.ffbz;格式化了以下,发现代码是错的
里面有两个t6
hust,我们可是同城学校哦。
select t1.sfid,t1.ffbh, t1.ffbz,t1.gwdj,t1.sjffjt,
t2.gwmc,t2.gwlx, t2.jhjt, t4.xh, t4.sxlbm,
t4.xjzt,t4.rxny,
t4.ywszzg,
t5.nj,
t6.dwmc,
t8.xm
from hust_sam_ast_monthdeliver t1 inner join hust_sam_ast_post t2 on t2.gwbh = t1.gwbh
inner join hust_xs_jb t4 on t4.sfid = t1.sfid inner join hust_xx_class t5 on t5.bjbh = t4.bjbh
inner join hust_xx_department t6 on t6.DWBH = t5.DWBH
inner join hust_xx_identity t7 on t7.sfid = t1.sfid
inner join hust_xx_profile t8 on t8.uuid = t7.uuid
where (t4.sxlbm='21' and t1.sjffjt>800) or (t4.sxlbm='25' and t1.sjffjt>1200 and substr(t4.rxny,1,4)<2009)or(t4.sxlbm='25' and t1.sjffjt>1500 and substr(t4.rxny,1,4)>2009)
and t1.ffny=#ffny#
order by t1.ffbz asc
SQL> select 1 from dual where substr('2008sr',1,4) < 2009; 1
----------
1SQL> select 1 from dual where substr('2008sr',1,4) > 2009; 1
----------