select u.name,o.name,affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
)
大概是这么个感觉,affiliation是别的数据库中查出来的值,我放到查询参数中,想要随着查询结果一并查出。
user和org两张表中没有affiliation字段,所以会报错,怎么能声明affiliation为临时字段好查出结果或别的什么方法?原sql是用ibatis+mysql写的:
select u.name as user_name,o.name as org_name,affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
<iterate property="member" open="(" close=")" conjunction=" or ">
o.identity = $member[].nsrsbh$
and u.user_id = $member[].user_id$
and affiliation = $member[].user_id$
</iterate>
)
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
)
大概是这么个感觉,affiliation是别的数据库中查出来的值,我放到查询参数中,想要随着查询结果一并查出。
user和org两张表中没有affiliation字段,所以会报错,怎么能声明affiliation为临时字段好查出结果或别的什么方法?原sql是用ibatis+mysql写的:
select u.name as user_name,o.name as org_name,affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
<iterate property="member" open="(" close=")" conjunction=" or ">
o.identity = $member[].nsrsbh$
and u.user_id = $member[].user_id$
and affiliation = $member[].user_id$
</iterate>
)
select 别的数据库中查出来的值 into affiliation from ....
再运行你的SQL语句
select 别的数据库中查出来的值 into affiliation from ....
再运行你的SQL语句
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
)
select u.name,o.name,@affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
);
如果affiliation中的值是1个
select u.name,o.name,(select 别的数据库中查出来的值 from ...) as affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
);
select u.name,o.name,case when o.identity= '11101057601' then '1222' else '5555' end affiliation
from user u,org o
where o.org_id = u.org_id
and o.state = '1'
and u.state = '1'
and (
(o.identity ='11101057601' and u.user_id = '123456' and affiliation = '1222')
or (o.identity ='00000' and u.user_id = '10000000' and affiliation = '5555')
)