昨天碰到一道题,当时没搞定回来想了想才解决。
表格t各字段分别是A,B,C,D,E,要求筛选按E分组后各组中D值大于D的平均数的所有行。
Group by 'E'
和Where|Having D>avg(D)
肯定是要用到的,但是分组后筛选ABC字段也需要保留。试验了下,用了Group by
之后的Select A,B,C From t Group by……
只会对各组的第一行过滤后保存。
比如下面的例子
- 表fruits的数据 mysql> select * from fruits order by sid;
sid | fname | fprice |
---|---|---|
101 | apple | 5.30 |
101 | blackberry | 15.00 |
102 | orange | 10.30 |
102 | melon | 8.50 |
103 | lemon | 6.10 |
103 | cherry | 9.90 |
103 | coconut | 9.50 |
104 | orange | 10.50 |
104 | apple | 5.80 |
105 | cherry | 9.50 |
- 使用Group by
语句后的查询结果 Select * from fruits Group by sid;
结果中只有各组第一行被查询到
sid | fname | fprice |
---|---|---|
101 | apple | 5.30 |
102 | orange | 10.30 |
103 | lemon | 6.10 |
104 | orange | 10.50 |
105 | cherry | 9.50 |
- 使用过滤语句 Select * from fruits Group by sid having fprice > avg(fprice)
理所当然的失败
sid | fname | fprice |
---|---|---|
102 | orange | 10.30 |
104 | orange | 10.50 |
分组查询时,查询的列似乎只能是被分组列和集合函数,除非被分组的列(可能是多个)本身有唯一性特征,即每组只有一个数据,才能保证分组时没有数据丢失。
自学的时候没有看到书上介绍分组时有类似说明,只说了常和集合函数配合。只是到现在我试过的例子都支持这个判断。
关键是怎么在数据中引用各组的avg(fprice)
作为过滤条件。因为平均值对应多个组,使用子查询不好实现(我想不出来)。
后来是查询出各组的avg(fprice)和sid生成新表,然后和原表连接起来,再简单用where子句过滤。新表我选择了视图。
- 先生成视图 Create View v_fruit As Select avg(fprice) as avg_p, sid as s_id From fruits Group by sid;
avg_p | s_id |
---|---|
10.150000 | 101 |
9.400000 | 102 |
8.500000 | 103 |
8.150000 | 104 |
9.500000 | 105 |
- 然后通过左连接查询
Select sid, fname, fprice
From fruits left outer join v_fruit
on fruits.sid=v_fruit.s_id
Where fprice > avg_p;
sid | fname | fprice |
---|---|---|
101 | blackberry | 15.00 |
102 | orange | 10.30 |
103 | cherry | 9.90 |
103 | coconut | 9.50 |
104 | orange | 10.50 |
搞定,目前只能想到这个法子,感觉有点麻烦。