ORACLE基础 ——分组统计
<span lang="EN-US">1<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">组函数</span><span lang="EN-US"></span>
<p class="MsoNormal" style="text-indent:21.0pt"><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">所谓分组统计，即先分组，再统计。单纯的分组没有啥意思，我们关注的是分出来的各组的各种属性。比如我去买鞋子，看到百货商店里的鞋子分为“匡威”“耐克”“阿迪”和“内联升”等</span><span lang="EN-US">4</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">组，这些组本身对我们并不重要，对我们重要的是哪一组鞋最耐穿，哪一组的款式更适合我们。如果您想买双布鞋，您会去耐克专卖吗？（咦？我怎么扯了这么多废话，咱们挨踢</span><span lang="EN-US">er</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">们最不喜欢的就是废话，我还是赶紧切入正题吧，小弟下边再不说废话了。。。</span><span lang="EN-US">^^</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">）</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">相应的，数据库中的分组，也需要一些函数来求出这些组的某些属性，比如：</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span lang="EN-US">COUNT():<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">求出全部的记录数；</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span lang="EN-US">MAX():<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">求出一组中的最大值；</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span lang="EN-US">MIN():<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">求出一组中的最小值；</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="margin-left:21.0pt"><span lang="EN-US">AVG():<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">求出一组中的平均值；</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span lang="EN-US">SUM():<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">求和；</span><span lang="EN-US"></span></p>
<p class="MsoNormal" style="text-indent:21.0pt"><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">这些函数的实际效果就不举例子了，比较简单，大家自己试一试就行了，咱们还是把注意力放到分组统计上来。</span><span lang="EN-US"></span></p>
<span lang="EN-US">2<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">分组统计</span><span lang="EN-US"></span>
<p class="MsoNormal" style="text-indent:21.0pt"><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">先大致看一下语法格式，知道长什么模样就行，不用去背，枯燥无味，没啥意思。</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-spacerun:yes"> </span>SELECT ***</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-spacerun:yes"> </span>FROM ***</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-spacerun:yes"> </span>(WHERE </span><span style="font-size:10.5pt;
line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:
minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">条件</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%">)</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-spacerun:yes"> </span>(GROUP BY </span><span style="font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">分组条件</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%">)</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-spacerun:yes"> </span>(ORDER BY </span><span style="font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">排序字段</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"> ASC|DESC) </span><span style="font-size:10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">；</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">P.S.
</span><span style="font-size:10.5pt;line-height:115%;font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">用“</span><span lang="EN-US" style="font-size:
10.5pt;line-height:115%">()</span><span style="font-size:10.5pt;line-height:
115%;font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">”括起来的表示可有可无。</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<span lang="EN-US">2.1</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">示例用表</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span>
<p class="MsoNormal"><span lang="EN-US"><span style="mso-spacerun:yes">
</span></span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">以下示例均以</span><span lang="EN-US">Oracle 10g </span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">自带的</span><span lang="EN-US">emp</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">（雇员表）为基础。</span><span lang="EN-US"></span></p>
<span lang="EN-US">Emp</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">表</span><span lang="EN-US"></span><div><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082253zrlr.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082253zrlr.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></div>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<span lang="EN-US">2.2</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">普通情况</span><span lang="EN-US"></span>
<p class="MsoNormal"><span style="font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">范例：求出每个部门的雇员数量，应该按照部门编号进行分组</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">SELECT
deptno ,COUNT(empno)</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">FROM
emp</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">GROUP
BY deptno</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">;</span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_131208233205Xr.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_131208233205Xr.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><span style="font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">或者加上排序：</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">SELECT
deptno ,COUNT(empno)</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">FROM
emp</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">GROUP
BY deptno</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">ORDER
BY COUNT(EMPNO) DESC</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">;</span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082398zwtt.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082398zwtt.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<span lang="EN-US">2.3</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">错误</span><span lang="EN-US">1</span>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">SELECT
deptno, COUNT(empno) </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">FROM
emp</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">;</span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082431jSM4.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082431jSM4.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:11.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">这样就引入了规则一：</span></b><b style="mso-bidi-font-weight:
normal"><span lang="EN-US" style="font-size:11.0pt;mso-bidi-font-size:10.5pt;
line-height:115%"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">如果程序中使用了分组函数，则有两种可以使用的情况</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000"></span></b></p>
<p class="MsoListParagraphCxSpFirst" style="margin-left:18.0pt;mso-add-space:
auto;text-indent:-18.0pt;mso-list:l1 level1 lfo1"><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;mso-bidi-font-family:Calibri;mso-bidi-theme-font:
minor-latin;color:#C00000"><span style="mso-list:Ignore">1）<span style="font:7.0pt "Times New Roman""> </span></span></span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">如果要查询</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000">(SELECT)</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">某字段和分组函数，则必须使用</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">来制定分组条件；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto;text-indent:-18.0pt;mso-list:l1 level1 lfo1"><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;mso-bidi-font-family:Calibri;mso-bidi-theme-font:
minor-latin;color:#C00000"><span style="mso-list:Ignore">2）<span style="font:7.0pt "Times New Roman""> </span></span></span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">单独</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000">SELECT</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">分组函数，不加某个字段，这样就不用使用</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY;</span></b></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto;text-indent:-18.0pt;mso-list:l0 level1 lfo2"><span lang="EN-US" style="mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;
mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin"><span style="mso-list:Ignore">1)<span style="font:7.0pt "Times New Roman"">
</span></span></span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">效果如</span><span lang="EN-US">2.2</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">普通情况</span><span lang="EN-US"></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto;text-indent:-18.0pt;mso-list:l0 level1 lfo2"><span lang="EN-US" style="mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;
mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin"><span style="mso-list:Ignore">2)<span style="font:7.0pt "Times New Roman"">
</span></span></span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">效果如下：</span><span lang="EN-US"></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto"><span lang="EN-US">SELECT count(deptno)<span style="mso-tab-count:1"> </span>//</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">只有分组函数哦！</span><span lang="EN-US"></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto"><span lang="EN-US">FROM emp ;</span></p><p class="MsoListParagraphCxSpMiddle" style="margin-left:18.0pt;mso-add-space:
auto"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_13120824642Zyl.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_13120824642Zyl.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoListParagraphCxSpLast" style="margin-left:18.0pt;mso-add-space:auto"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<span lang="EN-US">2.4</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">错误</span><span lang="EN-US">2</span>
<p class="MsoNormal"><span lang="EN-US">SELECT t.mgr, t.deptno, count(*) cnt</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp t</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY t.mgr</span></p>
<p class="MsoNormal"><span lang="EN-US">ORDER BY cnt</span></p>
<p class="MsoNormal"><span lang="EN-US">;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082500hhhd.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082500hhhd.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:11.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">所以，规则二：</span></b><b style="mso-bidi-font-weight:
normal"><span lang="EN-US" style="font-size:11.0pt;mso-bidi-font-size:10.5pt;
line-height:115%"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">在使用分组函数的时候，不能出现分组函数和分组条件之外的字段；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">解决办法是：查询多少字段，就</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">多少字段</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">上边范例可改为：</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT t.mgr, t.deptno, count(*) cnt</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp t</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY t.mgr, t.deptno</span></p>
<p class="MsoNormal"><span lang="EN-US">ORDER BY cnt</span></p>
<p class="MsoNormal"><span lang="EN-US">;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082540Uurr.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082540Uurr.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<span lang="EN-US">2.5</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">错误</span><span lang="EN-US">3</span>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">要求显示出平均工资大于</span><span lang="EN-US">2000</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">的部门编号和平均工资</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT deptno,AVG(sal)</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp</span></p>
<p class="MsoNormal"><span lang="EN-US">WHERE AVG(sal)>2000</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY deptno ;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_13120825809udo.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_13120825809udo.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:11.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">所以，规则三：</span></b><b style="mso-bidi-font-weight:
normal"><span lang="EN-US" style="font-size:11.0pt;mso-bidi-font-size:10.0pt;
line-height:115%"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">分组函数只能在分组中使用，不允许在</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">WHERE</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">子句中出现，</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">如果要指定分组的条件，则只能通过第二种条件指令：</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">HAVING;</span></b></p>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">上边范例可修改为：</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">SELECT
t.deptno, AVG(sal)</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">FROM
emp t</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">GROUP
BY t.deptno</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%">HAVING
AVG(sal) > 2000 ;</span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082620wlCc.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082620wlCc.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<span lang="EN-US">2.6</span><span style="font-family:宋体;mso-ascii-font-family:
Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">错误</span><span lang="EN-US">4</span>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">分组函数的套嵌使用</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">范例：求出平均工资</span>
<span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:
minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">最高的部门工资；</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT deptno ,MAX(AVG(sal))</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY deptno ;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082655sv5v.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082655sv5v.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:11.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">所以，规则</span></b><b style="mso-bidi-font-weight:
normal"><span lang="EN-US" style="font-size:11.0pt;mso-bidi-font-size:10.0pt;
line-height:115%">4</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:11.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">：</span></b><b style="mso-bidi-font-weight:
normal"><span lang="EN-US" style="font-size:11.0pt;mso-bidi-font-size:10.0pt;
line-height:115%"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">分组函数可以嵌套使用，但是此时不能再出现分组条件的查询语句</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">(</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">即不能</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000">SELECT</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">要分组的字段</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">)</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><span style="font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">方法一，去掉</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%">deptno</span><span style="font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">：</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT MAX(AVG(sal))</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY deptno ;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082691WUoN.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082691WUoN.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">但是这样就不知道是哪个部门了。。。</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span style="font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">方法二，把想要查询的字段也写成函数：</span><span lang="EN-US"></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT MAX(deptno) ,MAX(AVG(sal))</span></p>
<p class="MsoNormal"><span lang="EN-US">FROM emp</span></p>
<p class="MsoNormal"><span lang="EN-US">GROUP BY deptno ;</span></p><p class="MsoNormal"><span lang="EN-US"><a href="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082720u5hI.png" target="_blank"><img src="http://blog.chinaunix.net/attachment/201107/31/25183449_1312082720u5hI.png" .load="imgResize(this, 650);" border="0" ;=""></a></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-no-proof:yes">
</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<span lang="EN-US">3<span style="mso-tab-count:1"> </span></span><span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">结束语</span><span lang="EN-US"></span>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"><span style="mso-tab-count:1"> </span></span><span style="font-size:10.5pt;
line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:
minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin">好了，常用的分组查询就是这些情况，简单做一下总结：</span><span lang="EN-US" style="font-size:10.5pt;line-height:115%"></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">规则一：如果程序中使用了分组函数，则有两种可以使用的情况</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000"></span></b></p>
<p class="MsoListParagraphCxSpFirst" style="margin-left:18.0pt;mso-add-space:
auto;text-indent:-18.0pt;mso-list:l1 level1 lfo1"><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;mso-bidi-font-family:Calibri;mso-bidi-theme-font:
minor-latin;color:#C00000"><span style="mso-list:Ignore">3）<span style="font:7.0pt "Times New Roman""> </span></span></span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">如果要查询</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000">(SELECT)</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">某字段和分组函数，则必须使用</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">来制定分组条件；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoListParagraphCxSpLast" style="margin-left:18.0pt;mso-add-space:auto;
text-indent:-18.0pt;mso-list:l1 level1 lfo1"><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;mso-bidi-font-family:Calibri;mso-bidi-theme-font:
minor-latin;color:#C00000"><span style="mso-list:Ignore">4）<span style="font:7.0pt "Times New Roman""> </span></span></span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">单独</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000">SELECT</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">分组函数，不加某个字段，这样就不用使用</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY;</span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000"> </span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">规则二：在使用分组函数的时候，不能出现分组函数和分组条件之外的字段；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">解决办法是：查询多少字段，就</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.5pt;line-height:115%;color:#C00000">GROUP BY</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.5pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">多少字段</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.5pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"> </span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">规则三：分组函数只能在分组中使用，不允许在</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">WHERE</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">子句中出现，</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">如果要指定分组的条件，则只能通过第二种条件指令：</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">HAVING;</span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;line-height:115%"> </span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">规则四：分组函数可以嵌套使用，但是此时不能再出现分组条件的查询语句</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">(</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">即不能</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000">SELECT</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;font-family:
宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;color:#C00000">要分组的字段</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;
mso-bidi-font-size:10.0pt;line-height:115%;color:#C00000">)</span></b><b style="mso-bidi-font-weight:normal"><span style="font-size:12.0pt;mso-bidi-font-size:
10.0pt;line-height:115%;font-family:宋体;mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:
minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;
color:#C00000">；</span></b><b style="mso-bidi-font-weight:normal"><span lang="EN-US" style="font-size:12.0pt;mso-bidi-font-size:10.0pt;line-height:115%;
color:#C00000"></span></b></p>
<p class="MsoNormal"><span lang="EN-US">P.S. </span><span style="font-family:宋体;
mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:
宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin">参考资料：</span><span lang="EN-US">MLDN Oracle</span><span style="font-family:宋体;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:
Calibri;mso-hansi-theme-font:minor-latin">资料</span><span lang="EN-US"></span></p>
页:
[1]