select distinct a.city_id,a.city_name,count(b.msisdn)
from region_info a,receive_user b
where a.city_id=substr(b.area_id,1,5)(+)
and a.province_id='10'
and b.end_date<to_char(sysdate,'yyyymmdd')
group by a.city_id,a.city_name
关键是这里:substr(b.area_id,1,5)(+)请问表达式为什么就不能当做左连接的条件?
from region_info a,receive_user b
where a.city_id=substr(b.area_id,1,5)(+)
and a.province_id='10'
and b.end_date<to_char(sysdate,'yyyymmdd')
group by a.city_id,a.city_name
关键是这里:substr(b.area_id,1,5)(+)请问表达式为什么就不能当做左连接的条件?
你可以先用子查询生成一个计算列,然后再连接
SELECT DISTINCT a.city_id, a.city_name, COUNT(b.msisdn)
FROM region_info a, (SELECT msisdn, end_date, substr(area_id, 1, 5) area_id receive_user) b
WHERE a.city_id = area_id(+) AND
a.province_id = '10' AND
b.end_date < to_char(SYSDATE, 'yyyymmdd')
GROUP BY a.city_id, a.city_name
--要嵌套写才行
select distinct a.city_id,a.city_name,count(b.msisdn)
from (select substr(area_id,1,5) area_id,msisdn,end_date
from receive_user
where end_date<to_char(sysdate,'yyyymmdd')) b,region_info a
where a.city_id=b.area_id(+) and a.province_id='10'
group by a.city_id,a.city_name
select a.city_id,a.city_name,count(distinct b.msisdn)
from region_info a,receive_user b
where a.city_id=substr(b.area_id(+),1,5)
and a.province_id='10'
and b.end_date<to_char(sysdate,'yyyymmdd')
group by a.city_id,a.city_name