如果仅仅是要取得企业的id,只需要查询表B就可以了。如下: select distinct b.enterprise_id from B b where (b.region_id="01"or b.region_id="02")如果要得到企业的相关信息,则需要进行关联: select distinct a.enterprise_id,a.enterparise_name,a.desc from A a,B b where a.enterprise_id=b.enterprise_id and (b.region_id="01"or b.region_id="02")
select distinct a.enterprise_id,a.enterparise_name,a.desc from A a,B b where a.enterprise_id=b.enterprise_id and (b.region_id="01"or b.region_id="02")这样会使企业信息重复!!!!!!!
如果一个企业属于两个地区,则上面的语句就有重复记录 select distinct a.enterprise_id,a.enterparise_name,a.desc from A a where a.enterprise_id= (select distinct b.enterprise_id from B b where b.region_id="01"or b.region_id="02")
楼主的问题我没有看得太懂,如果不想让企业重复可以考虑用union写sqlselect a.enterprise_id,a.enterparise_name,a.desc from A a,B b where a.enterprise_id=b.enterprise_id and b.region_id="01" union select a.enterprise_id,a.enterparise_name,a.desc from A a,B b where a.enterprise_id=b.enterprise_id and b.region_id="02" union ......
select a.enterprise_id,b.region_id from A a,B b where a.enterprise_id=b.enterprise_id and (b.region_id="01"or b.region_id="02") and rownum < 2 group by a.enterprise_id
不知道你是不是想让企业不重复找出相应的地区,如果是可以看看下面: SELECT A.enterprise_id , (SELECT MAX(B.region_id) FROM B WHERE B.enterprise_id = A.enterprise_id AND B.region_id IN ('01','02') GROUP BY B.enterprise_id ) AS region_id FROM A 区域有多个的时候只显示ID最大的一个,不知道合不合适。
Select a.enterprise_id,min(b.region_id) from A a,B b Where a.enterprise_id=b.enterprise_id and (b.region_id="01"or b.region_id="02") Group By a.enterprise_id
select distinct b.enterprise_id
from B b
where (b.region_id="01"or b.region_id="02")如果要得到企业的相关信息,则需要进行关联:
select distinct a.enterprise_id,a.enterparise_name,a.desc
from A a,B b
where a.enterprise_id=b.enterprise_id
and (b.region_id="01"or b.region_id="02")
select distinct a.enterprise_id,a.enterparise_name,a.desc
from A a,B b
where a.enterprise_id=b.enterprise_id
and (b.region_id="01"or b.region_id="02")这样会使企业信息重复!!!!!!!
select distinct a.enterprise_id,a.enterparise_name,a.desc
from A a
where a.enterprise_id= (select distinct b.enterprise_id from B b where
b.region_id="01"or b.region_id="02")
from A a,B b where a.enterprise_id=b.enterprise_id and b.region_id="01"
union
select a.enterprise_id,a.enterparise_name,a.desc
from A a,B b where a.enterprise_id=b.enterprise_id and b.region_id="02"
union
......
from A a,B b
where a.enterprise_id=b.enterprise_id and (b.region_id="01"or b.region_id="02") and rownum < 2
group by a.enterprise_id
SELECT A.enterprise_id
,
(SELECT MAX(B.region_id)
FROM B
WHERE B.enterprise_id = A.enterprise_id
AND B.region_id IN ('01','02')
GROUP BY B.enterprise_id
) AS region_id
FROM A 区域有多个的时候只显示ID最大的一个,不知道合不合适。
from A a,B b
Where a.enterprise_id=b.enterprise_id and
(b.region_id="01"or b.region_id="02")
Group By a.enterprise_id