如下语句该如何修改啊？

[josiahliang]

几千条电话充值数据中，有全国的数据。比如上海 021， 广州020，其他地区 等等


想统计每个地区，即每个省市有多少个充值。
网友提供的方法是
select case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end , count(1)
from tb
group by case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end

但我想显示每个地区的名字（比如 021 就显示上海 然后就是该地区电话充值的次数）就left Join 了两张表，如下


select case when left(a.tel_no,2) in ('01','02') then left(a.tel_no,3)
else left(a.tel_no,4)
end , c.area_name,count(1)
from tb a
left join table2 b on a.ars_id=b.ars_id
left join table3 c on b.area_id=c.area_id
where a.bill_time between '2011-03-01 00:00:000' and '2011-03-08 00:00:000' group by case when left(a.tel_no,2) in ('01','02') then left(a.tel_no,3)
else left(a.tel_no,4)
end , c.area_name、

但貌似结果不对。

[SQL77]

SELECT * FROM
(select case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end AS AAA , count(1) BBB
from tb
group by case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end) A left join table2 b on a.ars_id=b.ars_id
left join table3 c on b.area_id=c.area_id
where a.bill_time between '2011-03-01 00:00:000' and '2011-03-08 00:00:000'

[让你望见影子的墙]

建议给出数据，方便进行测试

[--小F--]

[Quote=引用 2 楼 herowang 的回复:]
建议给出数据，方便进行测试
[/Quote]
同意

[josiahliang]

什么数据啊？

[AcHerat]

[Quote=引用 4 楼 josiahliang 的回复:]

什么数据啊？
[/Quote]

你的目标表的数据，还有你地区区号表的数据！

[josiahliang]

tb表字段: tel_no,bill_time,productid,ars_id ...

table1表字段:ars_id,area_id ..

table2表字段:area_no,area_name,area_id ..

[josiahliang]

tb表字段: tel_no, bill_time,productid,ars_id
021624241 1

table1表字段:ars_id,area_id ..
1 , 1
2 , 2

table2表字段:area_no,area_name,area_id ..
010 北京市 11
021 上海 1

[josiahliang]

010 15
020 953
021 3917
023 1290
024 201

比如说上面的是如下语句的结果
select case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end , count(1)
from tb
group by case when left(tel,2) in ('01','02') then left(tel,3)
else left(tel,4)
end
那我想要如下效果该怎么写语句，我写的两个left join 是为了join地区表，但结果却不对，可能是语句写错了
010 北京 15
020 广州 953
021 上海 3917
023 重庆 1290

[SQL77]

SELECT * FROM  
(select case when left(tel,2) in ('01','02') then left(tel,3)
  else left(tel,4)
  end AS AAA , count(1) BBB
from tb a
where a.bill_time between '2011-03-01 00:00:000' and '2011-03-08 00:00:000' 
group by case when left(tel,2) in ('01','02') then left(tel,3)
  else left(tel,4)
  end) A left join table2 b on a.ars_id=b.ars_id
left join table3 c on b.area_id=c.area_id

[lds1ove]

[josiahliang]

SELECT * FROM
(select case when left(tel_no,2) in ('01','02') then left(tel_no,3)
else left(tel_no,4)
end AS AAA , count(1) BBB
from lo_ars_success
where bill_time between '2011-03-01 00:00:000' and '2011-03-08 00:00:000'
group by case when left(tel_no,2) in ('01','02') then left(tel_no,3)
else left(tel_no,4)
end) A left join sy_ars_info b on A.ars_id=b.ars_id
left join sy_area_no c on b.area_id=c.area_id

提示Invalid column name 'ars_id'.