select a.*,round(avg(b.UpDownExtent),3) as UpDownExtent from SumStock a left join a2008 b on a.CodeID=b.CodeID where
a.CodeID not in(select top " + topi + " CodeID from SumStock where GetPowerDate is null order by b.UpDownExtent desc,CodeID asc)
and GetPowerDate is null order by b.UpDownExtent desc,CodeID asc
这是我的语句,报错 选择列表中的列 'SumStock.CodeID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。我先描述下数据库
SumStock 表
CodeID char(6) 唯一
CodeName nvarchar(10)
GetPowerDate floata2008表
CodeID 不唯一
UpDownExtent float我想要的效果是round(avg(b.UpDownExtent),3) 这个结果排序 " + topi + " 这个是变量 如果为0就是第一个,
我自己发挥写了b.UpDownExtent 我知道是错的,可是不知道怎么办了请教如何解决?谢谢!
select a.字段列表,
round(avg(b.UpDownExtent),3) as UpDownExtent
from SumStock a left join a2008 b
on a.CodeID=b.CodeID
where a.CodeID not in
(
select top " + topi + " CodeID
from SumStock
where GetPowerDate is null
order by b.UpDownExtent desc,
CodeID asc
)
and GetPowerDate is null
GROUP BY a.字段列表....,b.UpDownExtent,
order by b.UpDownExtent desc,a.CodeID asc
tb
id cid n
1 1 4
2 1 5
3 1 23
4 2 9
4 2 11select cid,n from tb group by cid order by n当然报错了。为什么?select cid from tb group by cid
得到
1
2当你也要取n时, cid=1对应的n 有 4,5,23 那么它应该取哪一个,根本不知道。
cid=2的同理就像得到的结果是个 二行二列的表格
-------------
1 | ?
--------------
2 | ?
-------------
你让?部分到底填哪个?
你要 order by n , cid=1的n有 4,5,23, cid-2的n有9,11 那么它们用哪个和哪个比?
所以要么把它加在group by列表里,要么order by 时加聚合。 比如拿cid=1的n的最大值与 cid=2的n的最大值比较(当然,可能是sum也可能是avg,还可以是count)select cid from tb group by cid order by max(n)
基础的二维关系概念问题
--选择列表中用了聚合函数那就有个分组的概念,所以要加group by 子句。
select a.*,round(avg(b.UpDownExtent),3) as UpDownExtent from SumStock a left join a2008 b on a.CodeID=b.CodeID where
a.CodeID not in(select top " + topi + " CodeID from SumStock where GetPowerDate is null
group by a的字段列表
order by b.UpDownExtent desc,CodeID asc)
and GetPowerDate is null order by b.UpDownExtent desc,CodeID asc
--至于具体原理三楼说得很清楚。
用来求平均数
需要group by 子句
不能就会报错,建议楼主看看联机丛书上group by 子句的介绍
select a.CodeID,
round(avg(b.UpDownExtent),3) as UpDownExtent
from SumStock a left join a2008 b
on a.CodeID=b.CodeID
where a.CodeID not in
(
select top " + topi + " CodeID
from SumStock
where GetPowerDate is null
order by b.UpDownExtent desc,
CodeID asc
)
and GetPowerDate is null
GROUP BY a.CodeID,b.UpDownExtent
order by b.UpDownExtent desc,a.CodeID asc这样是把所有的都查询出来了,修改" + topi + "变量都无效了,我想当topi=0就是最大的,topi=1就是第二大的,以此类推。谢谢!