SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
结果是:
1 12849 12849 中国农业银行当涂县支行 aa 102414
2 12849 12849 中国农业银行当涂县支行 aa 103416
3 12849 12849 中国农业银行当涂县支行 正常 101704
但是在下面这句话执行的结果也跟上面一样,
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where test = 'aa'
结果跟上面一样,按道理的话,应该检索出两条记录才对啊,不知道怎么回事?求高手帮忙!
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
结果是:
1 12849 12849 中国农业银行当涂县支行 aa 102414
2 12849 12849 中国农业银行当涂县支行 aa 103416
3 12849 12849 中国农业银行当涂县支行 正常 101704
但是在下面这句话执行的结果也跟上面一样,
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where test = 'aa'
结果跟上面一样,按道理的话,应该检索出两条记录才对啊,不知道怎么回事?求高手帮忙!
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
) dd
where dd.test = 'aa'
试一下
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where id = 102414
结果:
1 12849 12849 中国农业银行当涂县支行 aa 102414
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,'bb',
3,'bb',
4,'aa',
5,'aa',
6,'bb') as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where test = 'aa' 这样看看 结果是什么样的后 再说或者把test='aa' 换成 test like 'aa'
我改成你的那种写法确实可以,
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,'bb') as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where test = 'aa'
这样也是可以的。
但不知道为什么下面的就不行了?
select * from
(
SELECT distinct c.COMPID,c.COMPCODE,c.COMPNAME,
decode(e.flowtype,
2,d10.LISTCONTENT,
3,d10.LISTCONTENT,
4,'aa',
5,'aa',
6,d9.LISTCONTENT) as test,
e.ID
from BIZ_SYS_COMP c INNER JOIN BIZ_SYS_COMPLAINFLOW e ON c.COMPID = e.COMPID
LEFT OUTER JOIN BIZ_SYS_COMPAP ap ON ap.COMPID = c.COMPID
LEFT OUTER JOIN BIZ_SYS_DD d10 ON to_char(c.status)=d10.LISTVALUE and d10.LISTKEY = 'COMP_STATUS'
LEFT OUTER JOIN BIZ_SYS_DD d11 ON to_char(ap.apstatus)=d11.LISTVALUE and d11.LISTKEY = 'COMP_APSTATUS'
LEFT OUTER JOIN BIZ_SYS_DD d9 ON CONCAT('4',e.re)=d9.LISTVALUE and d9.LISTKEY = 'COMP_APSTATUS'
WHERE 1=1 AND ((e.flowtype=4 and ap.apstatus<>0) or (e.flowtype<>4)) and e.flowtype <>1 AND c.COMPCODE like '12849%'
AND ( c.PROVINCECODE = 1)
)
where test = 'aa'