我写了一个统计语句如下:
select substr(t.zoneCode,0,2) as "地区代码",
(select name from zone a where a.code=substr(t.zoneCode,0,2)) as "地区名称",
count(*) as "人员数"
from userInfo t group by substr(t.zoneCode,0,2)
目的是统计各省有多少人。可是
(select name from zone a where a.code=substr(t.zoneCode,0,2)) as "地区名称",
这个地方总是有问题。好像不让这样用。
请高手指点一下,是哪里出了问题。难道分组后就不能再做子查询了?
select substr(t.zoneCode,0,2) as "地区代码",
(select name from zone a where a.code=substr(t.zoneCode,0,2)) as "地区名称",
count(*) as "人员数"
from userInfo t group by substr(t.zoneCode,0,2)
目的是统计各省有多少人。可是
(select name from zone a where a.code=substr(t.zoneCode,0,2)) as "地区名称",
这个地方总是有问题。好像不让这样用。
请高手指点一下,是哪里出了问题。难道分组后就不能再做子查询了?
as "地区名称",
count(*) as "人员数"
from userInfo t, zone z where z.code=substr(t.zoneCode,0,2) group by substr(t.zoneCode,0,2), z.name
感觉如果
(select name from zone a where a.code=substr(t.zoneCode,0,2)) as "地区名称",
中如果一个substr(t.zoneCode,0,2)只搜出一笔数据的话MayBe可以通过?手上没装Oracle无法试验。
select r."地区代码",
(select name from zone a where a.code=r."地区代码") as "地区名称",
r."人员数"
from
(
select substr(t.zoneCode,0,2) as "地区代码",
count(*) as "人员数"
from userInfo t group by substr(t.zoneCode,0,2)
) r
from userInfo t, zone z where z.code=substr(t.zoneCode,0,2) group by substr(t.zoneCode,0,2), z.name;
四楼是正解,