有如下语句:
1(对应如下语句)
|
select (select m.policy_code from t_contract_master m where m.policy_id=1234)transfer,
2(对应下列语句)
|
(select m.policy_code from t_contract_master m where m.policy_id=5678)transfer from dual;
现在需要达到的效果是我结果(transfer)只取1,2其中一个,如何做呢?各位快帮帮忙!
1(对应如下语句)
|
select (select m.policy_code from t_contract_master m where m.policy_id=1234)transfer,
2(对应下列语句)
|
(select m.policy_code from t_contract_master m where m.policy_id=5678)transfer from dual;
现在需要达到的效果是我结果(transfer)只取1,2其中一个,如何做呢?各位快帮帮忙!
select (select m.policy_code from t_contract_master m where m.policy_id=1234)A,
(select m.policy_code from t_contract_master m where m.policy_id=5678)B from dual;
这样查询出来的结果A,B,但现在我只要A或是B单独存在(A,B条件是相悖的),哪个有值就取哪个,有没有办法呢
where (m.policy_id=1234 or m.policy_id = 45678)
and rownum = 1
where (m.policy_id=1234 or m.policy_id = 5678)
select m.policy_code from t_contract_master m
where (m.policy_id=1234 or m.policy_id = 45678)
order by policy_id )
where rownum = 1;这样可以满足LZ的要求。但优先关系先得通过大小关系来确定。不如5L方便,但貌似直观些。
这是第一个语句:(所带的条件是查询出来的transfer_from_policy等于当前保单)
sql.append(" (select distinct(m.policy_code) from t_contract_master m ,t_cash t ");
sql.append("where t.policy_id=m.policy_id and t.fee_id in (");
sql.append(" select f.fee_id from t_arap_offset f where f.offset_id in(");
sql.append(" select ao.offset_id from t_arap_offset ao,t_cash tc");
sql.append(" where ao.fee_id = tc.fee_id and ao.arap_table=1 and ao.offset_transfer='Y'");
sql.append(" and tc.policy_id="+receivablePayableVO.getPolicyId());
sql.append(") and f.arap_table=1 and f.offset_amount<0 and f.offset_transfer='N')" );
sql.append(" and exists(select 1 from t_contract_master m ,t_cash tc where m.policy_id=tc.policy_id");
sql.append(" and tc.fee_id in(");
sql.append(" select taf.fee_id from t_arap_offset taf,t_cash tc where taf.fee_id=tc.fee_id and tc.fee_id = c.fee_id and taf.offset_id in(");
sql.append(" select ola.offset_id from t_prem_arap a, t_arap_offset ola where ola.fee_id = a.list_id");
sql.append(" and a.list_id in ("+receivablePayableVO.getFeeId()+") and ola.arap_table = 4 ) and tc.pay_mode=5 and tc.fee_type=11)");
sql.append(" and m.policy_id="+receivablePayableVO.getPolicyId()+")) TRANSFER_FROM_POLICY,");
这是第二个语句:(所带的条件是查询出来的transfer_from_policy不等于当前保单)
sql.append(" (select cm.policy_code from t_contract_master cm where cm.policy_id = "+receivablePayableVO.getPolicyId());
sql.append(" and exists(select 1 from t_contract_master m ,t_cash tc where m.policy_id=tc.policy_id");
sql.append(" and tc.fee_id in(");
sql.append(" select taf.fee_id from t_arap_offset taf,t_cash tc where taf.fee_id=tc.fee_id and tc.fee_id = c.fee_id and taf.offset_id in(");
sql.append(" select ola.offset_id from t_prem_arap a, t_arap_offset ola where ola.fee_id = a.list_id");
sql.append(" and a.list_id in ("+receivablePayableVO.getFeeId()+") and ola.arap_table = 4 ) and tc.pay_mode=5 and tc.fee_type=11)");
sql.append(" and cm.policy_id <> m.policy_id)) TRANSFER_FROM_POLICY,");
在jsp中拿值显示的时候就是拿TRANSFER_FROM_POLICY,但是现在有两个,不知如何取舍(要达到的效果相当于第一语句有值就拿第一个,第二有值就拿第二个)
个人习惯不主张长sql语句。
有时候,只要能解决问题,不在乎要一个SQL还是多个SQL实现。