两张表之间进行数据库查询时的聚合函数用法
注意：表中内容中文显示时有乱码现象，我都用Hello World代替!
【comment(评论表)】：
| Bookid | userid | comment | star |
| ts00001 | 1 | Hello World | 4 |
| ts00001 | 2 | Hello World | 6 |
| ts00001 | 5 | Hello My Java | 9 |
| ts00002 | 1 | 123 | 4 |
| ts00002 | 2 | 123 | 4 |
| ts00003 | 2 | Hello World | 4 |
| ts00004 | 2 | Hello World | 4 |
| ts00005 | 1 | Hello World | 4 |
| ts00006 | 2 | Hello World | 4 |
| ts00007 | 1 | Hello World | 4 |
| ts00008 | 2 | Hello World | 4 |
| ts00009 | 1 | Hello World | 4 |
| ts00010 | 2 | Hello World | 4 |
| ts00011 | 2 | safdsa | 4 |
| ts00012 | 1 | sdfasd | 4 |
【Books(图书表)】：
| bookid | name | author | introduce | publisher | publicationtime | inventory | images | istop | categorycode | isbn |
| ts00001 | C# | Joe Mayo | Hello World | Hello World | 1900-01-01 00:00:00.000 | 5 | ../img/books/ts00001.jpg | True | TP312 510 | 12312312312 |
| ts00002 | SQL Server | []Solid Quality Learning | Hello World | Hello World | 2007-02-01 00:00:00.000 | 5 | ../img/books/ts00002.jpg | True | TP312 840 | 9787302163305 |
| ts00003 | ASP.NET2.0 | []Chris Hart John Kauffman | Hello Funs | Hello World | 1900-01-01 00:00:00.000 | 1 | ../img/books/ts00003.jpg | True | TP312 830 | 978730223305 |
| ts00004 | C | Hello World | Hello World | Hello World | 2010-06-01 00:00:00.000 | 7 | ../img/books/ts00004.jpg | True | TP312 630 | 9787302224464 |
| ts00005 | C++ Primer | Hello WorldStanley | Hello World | Hello World | 2006-03-01 00:00:00.000 | 5 | ../img/books/ts00005.jpg | True | TP312 820 | 9787115145543 |
| ts00006 | Java | []Bruce Eckel | Hello World | Hello World | 2008-12-01 00:00:00.000 | 6 | ../img/books/ts00006.jpg | True | TP312 510 | 9787111256113 |
| ts00007 | Hello World | Hello World | Hello World | Hello World | 2007-12-01 00:00:00.000 | 5 | ../img/books/ts00007.jpg | True | TP312 130 | 9787302162063 |
| ts00008 | Hello World | Hello World | Hello World | Hello World | 2010-01-01 00:00:00.000 | 5 | ../img/books/ts00008.jpg | True | TP312 220 | 9787115216878 |
| ts00009 | Hello World | Hello World | Hello World | Hello World | 2004-02-01 00:00:00.000 | 4 | ../img/books/ts00009.jpg | True | TP312 110 | 9787111135104 |
| ts00010 | Hello World | Hello World | Hello World | Hello World | 2011-01-01 00:00:00.000 | 4 | ../img/books/ts00010.jpg | True | TP312 150 | 9787111321330 |
| ts00011 | Hello World | Hello World | Hello World | Hello World | 2009-08-01 00:00:00.000 | 3 | ../img/books/ts00001.jpg | True | TP312 500 | 9787810821698 |
| ts00012 | PowerPoint | Hello World | PowerPoint 2007Hello World | Hello World | 2009-03-01 00:00:00.000 | 5 | ../img/books/ts00011.jpg | True | TP312 430 | 9787122046901 |
【问题是】现在我要查询两张表中comment中(bookid,name,author,introduce images)和books中(star)并且star要返回平均值，最后结果按bookid,name,author,introduce,images分组!
【查询语法】：
select top 12 c.bookid,left(b.name,10) as name,left(b.author,10) as author,left(b.introduce,40) as introduce,b.images,avg(c.star) as star from Books b inner join comment c on b.bookid=c.bookid group by c.bookid,name,author,introduce,images having avg(c.star)>=4
【结果为】:
| bookid | name | author | introduce | images | star |
| ts00001 | C# | Joe Mayo | Hello World | ../img/books/ts00001.jpg | 6 |
| ts00002 | SQL Server | [美]Solid Q | 《SQLServer2005实现与维护(附光盘MCTS教程)》是微软认证技术专家 | ../img/books/ts00002.jpg | 4 |
| ts00003 | ASP.NET2.0 | [美]Chris H | Hello Funs | ../img/books/ts00003.jpg | 4 |
| ts00004 | C程序设计（第四版） | 谭浩强 著 | 由谭浩强教授著、清华大学出版社出版的《C程序设计》是一本公认的学习C语言程序设计 | ../img/books/ts00004.jpg | 4 |
| ts00005 | C++ Primer | ：（美）Stanle | 作为目前业界广泛使用的编程语言，C++可谓包罗万象、博大精深。20年来，讲述C+ | ../img/books/ts00005.jpg | 4 |
| ts00006 | Java编程思想 | [美]Bruce E | 本书赢得了全球程序员的广泛赞誉，即使是最晦涩的概念，在Bruce Eckel的文 | ../img/books/ts00006.jpg | 4 |
| ts00007 | 大话设计模式 | 程杰 | 本书通篇都是以情景对话的形式，用多个小故事或编程示例来组织讲解GoF（设计模式的 | ../img/books/ts00007.jpg | 4 |
| ts00008 | 代码整洁之道 | （美）马丁 | 作者Martin是软件工程领域的大师级人物，是《敏捷软件开发：原则、模式与实践》 | ../img/books/ts00008.jpg | 4 |
| ts00009 | 计算机程序的构造和解 | ：[美]艾伯森 | 这一版本中强调了几个新问题，其中最重要的是有关的不同的途径中，计算模型里对于时间 | ../img/books/ts00009.jpg | 4 |
| ts00010 | 深入理解计算机系统（ | （美）布莱恩特，奥哈 | 本书从程序员的视角详细阐述计算机系统的本质概念，并展示这些概念如何实实在在地影响 | ../img/books/ts00010.jpg | 4 |
| ts00011 | 单片机原理与实用技术 | 付晓光 | 本书以人们对新知识的认识过程为顺序，从分析单片机的应用实例出发，以MCS-51系 | ../img/books/ts00001.jpg | 4 |
| ts00012 | PowerPoint | 墨思客工作室 | PowerPoint 2007是Microsoft公司推出的Office 200 | ../img/books/ts00011.jpg | 4 |
【总结:】
1、select 子句只能包含集合函数和出现在Group by子句中的分组列
2、集合函数只能应用于select子句和Having子句
3、使用Having子句之前必须使用Group by子句对数据行分组
4、Having子句中的条件表达式必须使用集合函数来构造
【关键点】：
千万不要在group by 子句中加star,因为加了以后，查询就不会返回平均值，而是返回分组，千万注意！
