select co.vchr_country country,p.vchr_province province,ci.name_zhs city,count.cnt
,
from c_city ci,c_province p,c_country co,
(select count(*) cnt from t_member m, t_supply s
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) count
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
order by province,count desc然后在t_supply.vchr_message_type加上索引.如果还是慢,看看执行计划.
,
from c_city ci,c_province p,c_country co,
(select count(*) cnt from t_member m, t_supply s
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) count
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
order by province,count desc然后在t_supply.vchr_message_type加上索引.如果还是慢,看看执行计划.
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,(
select count(*) count from t_member m, t_supply s
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) b
from a,b
order by a.province,b.count desc
select a.country,a.province,a.city,b.count from
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,(
select count(*) count from t_member m, t_supply s
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) border by a.province,b.count desc
这次就说ci.city_id invalide identifier.
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,(
select count(*) count from t_member m, t_supply s
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) b
order by a.province,b.count desc
select a.country,a.province,a.city,b.count
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,(
select count(*) count
from t_member m, t_supply s,c_city ci
where s.vchr_message_type='卖'
and m.num_member_id = s.num_member_id
and m.num_city_id=ci.city_id
) b
from a,b
order by a.province,b.count desc
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city,ci.city_id
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,
(
select m.num_city_id, count(*) count from t_member m, t_supply s
where s.vchr_message_type='卖' and m.num_member_id = s.num_member_id
group by m.num_city_id
) b
where a.city_id=b.num_city_id
order by a.province,b.count desc
(
select co.vchr_country country,p.vchr_province province,ci.name_zhs city,ci.city_id
from c_city ci,c_province p,c_country co
where ci.province_id=p.num_province_id
and p.num_country_id=co.num_country_id
) a,
(
select m.num_city_id, count(*) count from t_member m, t_supply s
where s.vchr_message_type='卖' and m.num_member_id = s.num_member_id
group by m.num_city_id
) b
where a.city_id=b.num_city_id(+)
order by a.province,b.count desc好象应该是用左连接的