sqlserver数据库学习感悟（1）----关于group by

以下含编写过程，如果嫌啰嗦，最后有总结哒！

例题：有heat表和eatables两张表，分别为：

eatables

heat：protein（蛋白质），fat（脂肪），sugar（含糖量），kj（热量）

平均热量最高的食物是哪一类：

当我们这样写时：

select type,avg(kj)
from heat,eatables
group by type

结果为：

这个结果计算的是所有食物的平均热量，没有将两张表联系起来

select avg(kj) from heat

所以改进为：

select avg(kj)
from heat,eatables
group by type
having heat.id=eatables.id

这样写也是不对的，会报：

HAVING 子句中的列 'heat.id’无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

所以having中的列名，必须包含在group by中

如果我们将heat.id和eatables.id放到group by中

select avg(kj),type
from heat,eatables
group by type,heat.id,eatables.id
having heat.id=eatables.id

就相当于按照每个食品的id分，即相当于没有分，得到结果为：

所以最后应该写为：where .....group by

select avg(kj),type
from eatables,heat
where eatables.id=heat.id
group by type

结果为：

平均热量最高的食物：

select top 1 type, avg(kj) 平均热量
from eatables,heat
where eatables.id=heat.id
group by type
order by avg(kj) desc

最后结果得：

再来看一个例子:

检索每个食品类别中，蛋白质含量最高的食物：

select type,name,max(protein)
  from heat,eatables
  where heat.id=eatables.id
  group by type

这样写是错误的：选择列表中的列 'eatables.name’无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

接下来就是处理name的问题了：

同理，如果将name写入group by，得到的结果是每个食品的蛋白质含量

select name
from heat,eatables
where heat.id=eatables.id
and protein = max(protein) 
group by type

这样写也是错的：聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

就算把protein移动到group by也是报错

select name
from heat,eatables
where heat.id=eatables.id 
group by type
having protein = max(protein)

错误1：name不在聚合函数或group by中

错误2：protein不在聚合函数或group by中

如果我们将name或者protein加入group by则会出现所有食品，一切都是徒劳！

所以正确的写法应为：

select name,a.type,max
from(select type,max(protein) max 
from eatables,heat
where heat.id = eatables.id
group by type)p,eatables as a,heat as b
where b.protein=p.max and a.id=b.id

新建一个表，包含type和其最高蛋白质含量，最后加入name

所以总结下来：

错误：HAVING 子句中的列 'heat.id’无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

解决措施：select 和group by中的列名应该出现在group by中，如果有其他列名可以写在where中，实在不行，考虑在查询中再建立一个表进行查询

错误：聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

解决措施：where中不能sum(),avg(),max(),min()等聚合函数，但是聚合函数可以位于having子句中，或选择列表所包含的子查询中，并且对其进行聚合的列是外部引用（就像上面最后一个例子那样）。

刚学数据库，不要向我这样犯低级错误啦，最后最后如果这篇文章对你有帮助，能给我个赞吗！！