group by语句中的子查询

Question

以前经常这样写：
select id,count(*) num,
  (select name from t0 where id=t1.id) as name
from t1
group by id
order by num desc
今天突然发现，改成这样会报错：
select id,count(*) num,
  (select name from t0 where id=v1.id) as name
from 
(select id+0 as id from t1)v1
group by id
order by num desc
提示(select name from t0 where id=v1.id)
not a group by expression大家有没有碰到过，怎样解决的？
我用的是9i，10和11不知有没有这个问题
我觉得可以算是oracle的bug吧

Answer 1

select id,count(*) num,
  (select name from t0 where id=v1.id) as name
from 
(select id+0 as id from t1)v1
group by id+0
order by num desc

Answer 2

对应关系：t0.id = t1.id + 10000
select id,count(*) num,
  (select name from t0 where id=t1.id+10000) as name
from t1
group by id
order by num desc
这样是没问题的
但现在改成：
create or replace view v1 as
select (t1.id+10000) as id from t1
with read only;select id,count(*) num,
  (select name from t0 where id=v1.id) as name
from v1
group by id
order by num desc;
当然拉，也不是非这样不可，我不钻牛角尖
只是听听高手的建议

Answer 3

可能是t0.id与v1.id是一对多的关系

Answer 4

select id,count(*) num,
  (select name from t0 where id=v1.id) as name
from 
(select id+0 as id1, id from t1)v1
group by id
order by num desc
写成以上SQL应该可以的, 原因是:group by 后面只能是实际列, 或是引用列
而id+0是一个表达式(id1只是这个表达式的一个标识而已, 在数据库中并不存在)

Answer 5

进一步发现问题：
select id+10000,count(*) num, 
  (select name from t0 where id=t1.id+10000) as name 
from t1 
group by id+10000
order by num desc
也报错
估计oracle会先把视图分解成这种语句我还试过用with...as的视图也同样问题

Answer 6

group by 语句中的 select 子句只能出现 group by 出现字段名称，其他字段名称只能作为聚合函数如 count()的参数出现。

Answer 7

最终测试结果：
select id,count(*) num,
  (select name from t0 where id=v1.id) as name
from 
(select id+1000 as id from t1 order by id)v1
group by id
order by num desc
只要视图或子查询足够复杂，无法分解到主查询语句中，就会生成临时表，这样就通过了
谢谢各位参与，给了我很大启发