子查询与group by 问题

[xeppp]

今天又学了一招

呵呵 上学时候 不知道 那位 老师说的 “子查询里不能放order by 语句”

select * from (
select top 9* from
(select top 30 * from db_owner.[function] order by 排序字段 asc)
as a order by 排序字段 desc
)as b
order by 排序字段 asc 粘出来分享一下

现在有一弱问题是
select * from
(select cityid,cityname from db_owner.city
)a 是可以执行的

但是 加了一个组函数 就不成了
select * from
(select cityid,cityname,count(provinceid) from db_owner.city
group by cityid,cityname
)a
请解释一下 谢谢

[friendlyFour]

select * from
(select cityid,cityname,count(provinceid) as aa from db_owner.city
group by cityid,cityname
)a

[liangCK]

子查询不能order by..除非也指定了top

[中国风]

取22-30

[中国风]

select * from
(select cityid,cityname,count(provinceid)provinceid from db_owner.city --少了列名
group by cityid,cityname
)a

[-狙击手-]

但是 加了一个组函数 就不成了
select * from
(select cityid,cityname,count(provinceid) from db_owner.city
group by cityid,cityname
)a
请解释一下 谢谢
--------------------------------------

count(provinceid) 必须指定一个列名



select * from
(select cityid,cityname,count(provinceid) as cnt from db_owner.city
group by cityid,cityname
)a

[lff642]

但是 加了一个组函数 就不成了
select * from
(select cityid,cityname,count(provinceid) from db_owner.city
group by cityid,cityname
)a
请解释一下 谢谢
-------------------------------------
给汇总列加个别名就好了

select * from
(select cityid,cityname,count(provinceid) provinceid from db_owner.city
group by cityid,cityname
) a