110¸öOracle ³£Óú¯ÊýµÄ×ܽá2

60.AVG(DISTINCT|ALL)
all±íʾ¶ÔËùÓеÄÖµÇóƽ¾ùÖµ,distinctÖ»¶Ô²»Í¬µÄÖµÇóƽ¾ùÖµ
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
Óï¾äÒÑ´¦Àí¡£
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(gao,1111.11);
SQLWKS> insert into table3 values(zhu,5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
Çó×î´óÖµ,ALL±íʾ¶ÔËùÓеÄÖµÇó×î´óÖµ,DISTINCT±íʾ¶Ô²»Í¬µÄÖµÇó×î´óÖµ,ÏàͬµÄֻȡһ´Î
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
Çó×îСֵ,ALL±íʾ¶ÔËùÓеÄÖµÇó×îСֵ,DISTINCT±íʾ¶Ô²»Í¬µÄÖµÇó×îСֵ,ÏàͬµÄֻȡһ´Î
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
Çó±ê×¼²î,ALL±íʾ¶ÔËùÓеÄÖµÇó±ê×¼²î,DISTINCT±íʾֻ¶Ô²»Í¬µÄÖµÇó±ê×¼²î
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
ÇóЭ·½²î
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
Ö÷ÒªÓÃÀ´¶ÔÒ»×éÊý½øÐÐͳ¼Æ
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
¶Ô·Ö×éͳ¼ÆÔÙ¼ÓÏÞÖÆÌõ¼þ
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
ÓÃÓÚ¶Ô²éѯµ½µÄ½á¹û½øÐÐÅÅÐòÊä³ö
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;