问题需求重现:“如果一个学员,已经填写了班车信息,就把操作那两个字改成修改”
当查询一个列表的时候获取此列表id结果作为另一个sql的是否存在的条件处理。
原来列表sql
SELECT S.BACK_ID,
S.S_CLASS_INFO_ID,
S.BACK_NAME,
TO_CHAR(S.END_DATE, 'yyyy-mm-dd HH24:mi'),
S.PROMPT_INFO
FROM S_CLASS_BACK_MAIN S
WHERE (S.IS_PUB = 'Y' OR S.PUB_DATE > SYSDATE)
AND S.IS_DEL = 'N'
AND S.S_CLASS_INFO_ID = 566
查看此back_id和此人user_id是否存在记录
SELECT SCBO.OPTION_ID, SCBO.BACK_DESC,scbo.back_id
FROM S_CLASS_BACK_USER SCBU,
S_CLASS_BACK_OPTION SCBO,
S_CLASS_BACK_MAIN SCBM
WHERE SCBU.USER_ID = 44075
AND SCBU.OPTION_ID = SCBO.OPTION_ID
AND SCBO.BACK_ID = SCBM.BACK_ID
AND SCBM.BACK_ID = 1142
综合在一个sql来写(避免在遍历主list得到back_id时候再去sql查询数据库,这样数据多了会很慢,影响效率)
SELECT S.BACK_ID,
S.S_CLASS_INFO_ID,
S.BACK_NAME,
TO_CHAR(S.END_DATE, 'yyyy-mm-dd HH24:mi'),
S.PROMPT_INFO,
nvl(to_char(tempResult.Option_Id),'null') AS isExist
FROM S_CLASS_BACK_MAIN S,(SELECT SCBO.OPTION_ID, SCBO.BACK_DESC,scbo.back_id
FROM S_CLASS_BACK_USER SCBU,
S_CLASS_BACK_OPTION SCBO,
S_CLASS_BACK_MAIN SCBM
WHERE SCBU.USER_ID = 44075
AND SCBU.OPTION_ID = SCBO.OPTION_ID
AND SCBO.BACK_ID = SCBM.BACK_ID) tempResult
WHERE (S.IS_PUB = 'Y' OR S.PUB_DATE > SYSDATE)
AND S.IS_DEL = 'N'
AND S.S_CLASS_INFO_ID = '566'
AND tempResult.Back_Id(+)=s.back_id