sql中的 where 、group by 和 having 用法解析

--sql中的 where 、group by 和 having 用法解析

--如果要用到group by 一般用到的就是“每這個(gè)字” 例如說明現(xiàn)在有一個(gè)這樣的表：每個(gè)部門有多少人 就要用到分組的技術(shù)

select DepartmentID as '部門名稱',COUNT(*) as '個(gè)數(shù)' from BasicDepartment group by DepartmentID
--這個(gè)就是使用了group by +字段 進(jìn)行了分組，其中我們就可以理解為我們按照了部門的名稱ID
--DepartmentID將數(shù)據(jù)集進(jìn)行了分組；然后再進(jìn)行各個(gè)組的統(tǒng)計(jì)數(shù)據(jù)分別有多少；
--如果不用count(*) 而用類似下面的語法
select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID

--將會出現(xiàn)錯(cuò)誤
--選擇列表中的列 'BasicDepartment.DepartmentName' 無效，因?yàn)樵摿袥]有包含在聚合函數(shù)或 GROUP BY 子句中。
  這就是我們需要注意的一點(diǎn)，如果在返回集字段中，這些字段
  要么就要包含在Group By語句的后面,作為分組的依據(jù)；
  要么就要被包含在聚合函數(shù)中,作為分組的依據(jù)；
--出現(xiàn)的錯(cuò)誤詳解:咱們看看group by 的執(zhí)行的過程,先執(zhí)行select 的操作返回一個(gè)程序集，
--然后去執(zhí)行分組的操作，這時(shí)候他將根據(jù)group by 后面的字段
--進(jìn)行分組，并且將相同的字段并稱一列數(shù)據(jù)，如果group by 后面沒有這個(gè)字段的話就要分成好多的數(shù)據(jù)。
--但是分組就只能將相同的數(shù)據(jù)分成兩列數(shù)據(jù)，而一列中又只能放入一個(gè)字段，所以那些沒有進(jìn)行分組的
--數(shù)據(jù)系統(tǒng)不知道將數(shù)據(jù)放入哪里，所以就出現(xiàn)此錯(cuò)誤
--目前一種分組情況只有一條記錄，一個(gè)數(shù)據(jù)格是無法放入多個(gè)數(shù)值的，
--所以這里就需要通過一定的處理將這些多值的列轉(zhuǎn)化成單值，然后將其放在對應(yīng)的
--數(shù)據(jù)格中，那么完成這個(gè)步驟的就是聚合函數(shù)。這就是為什么這些函數(shù)叫聚合函數(shù)(aggregate functions)了

--group by all語法解析：
--如果使用 ALL 關(guān)鍵字，那么查詢結(jié)果將包括由 GROUP BY 子句產(chǎn)生的所有組，即使某些組沒有符合搜索條件的行。
--沒有 ALL 關(guān)鍵字，包含 GROUP BY 子句的 SELECT 語句將不顯示沒有符合條件的行的組。
select DepartmentID,DepartmentName as '部門名稱',
COUNT(*) as '個(gè)數(shù)' from BasicDepartment group by all DepartmentID,DepartmentName

==========================================================================================================

--group by 和having 解釋：前提必須了解sql語言中一種特殊的函數(shù)：聚合函數(shù)，
例如SUM, COUNT, MAX, AVG等。這些函數(shù)和其它函數(shù)的根本區(qū)別就是它們一般作用在多條記錄上。

having是分組（group by）后的篩選條件，分組后的數(shù)據(jù)組內(nèi)再篩選
where則是在分組前篩選

where子句中不能使用聚集函數(shù)，而having子句中可以，所以在集合函數(shù)中加上了HAVING來起到測試查詢結(jié)果是否符合條件的作用。
即having子句的適用場景是可以使用聚合函數(shù)

having 子句限制的是組，而不是行
having 子句中的每一個(gè)元素也必須出現(xiàn)在select列表中。有些數(shù)據(jù)庫例外，如oracle

當(dāng)同時(shí)含有 where 子句、group by 子句 、having 子句及聚集函數(shù)時(shí)，執(zhí)行順序如下：
執(zhí)行where子句查找符合條件的數(shù)據(jù)；
使用group by 子句對數(shù)據(jù)進(jìn)行分組；對group by 子句形成的組運(yùn)行聚集函數(shù)計(jì)算每一組的值；最后用having 子句去掉不符合條件的組

  ex:  顯示每個(gè)地區(qū)的總?cè)丝跀?shù)和總面積．僅顯示那些面積超過1000000的地區(qū)。 
    SELECT region, SUM(population), SUM(area)
    FROM bbc
    GROUP BY region
    HAVING SUM(area)>1000000
    在這里，我們不能用where來篩選超過1000000的地區(qū)，因?yàn)楸碇胁淮嬖谶@樣一條記錄。(where子句中不能使用聚集函數(shù))
    相反，HAVING子句可以讓我們篩選成組后的各組數(shù)據(jù)

  ex:create TABLE Table1
    (
      ID int identity(1,1) primary key NOT NULL, 
      classid int,
      sex varchar(10),
      age int,
    ) 
    --添加測試多條數(shù)據(jù)
    Insert into Table1 values(1,'男',20)
    Insert into Table1 values(2,'女',22)
    Insert into Table1 values(3,'男',23)
    Insert into Table1 values(4,'男',22)
    Insert into Table1 values(1,'男',24)
    ..........
    查詢每一個(gè)班級中年齡大于20，性別為男的人數(shù)至少為2個(gè)的情況
    select COUNT(age) as '>20歲人數(shù)',classid from Table1 where sex='男' group by classid having COUNT(age)>2

底下這個(gè)例子很好
    SQL> select * from sc;

        SNO PNO    GRADE
      ---------- ----- ----------
YW       95
SX       98
YY       90
YW      89
SX       91
YY       92
YW      85
SX       88
YY       96
YW      95
SX       89
YY       88

    這個(gè)表所描述的是4個(gè)學(xué)生對應(yīng)每科學(xué)習(xí)成績的記錄，其中SNO（學(xué)生號）、PNO（課程名）、GRADE（成績）。

    1、顯示90分以上學(xué)生的課程名和成績

    //這是一個(gè)簡單的查詢，并沒有使用分組查詢

    SQL> select sno,pno,grade from sc where grade>=90;

        SNO PNO    GRADE
      ---------- ----- ----------
YW      95
SX       98
YY       90
SX       91
YY       92
YY       96
YW      95

    2、顯示每個(gè)學(xué)生的成績在90分以上的各有多少門

    --進(jìn)行分組顯示，并且按照where條件之后計(jì)數(shù)

    SQL> select sno,count(*) from sc where grade>=90 group by sno;

        SNO  COUNT(*)
    -  --------- ----------
     3
     2
     1
     1

    3、這里我們并沒有使用having語句，接下來如果我們要評選三好學(xué)生，條件是至少有兩門課程在90分以上才能有資格，
      列出有資格的學(xué)生號及90分以上的課程數(shù)。

    //進(jìn)行分組顯示，并且按照where條件之后計(jì)數(shù)，在根據(jù)having子句篩選分組

    SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2；

        SNO  COUNT(*)
    ---------- ----------
     3
     2

    這個(gè)結(jié)果是我們想要的，它列出了具有評選三好學(xué)生資格的學(xué)生號，跟上一個(gè)例子比較之后，發(fā)現(xiàn)這是在分組后進(jìn)行的子查詢。

    4、學(xué)校評選先進(jìn)學(xué)生，要求平均成績大于90分的學(xué)生都有資格，并且語文課必須在95分以上，請列出有資格的學(xué)生

    //實(shí)際上，這個(gè)查詢先把語文大于95分的學(xué)生號提取出來，之后求平均值，分組顯示后根據(jù)having語句選出平均成績大于90的

    SQL> select sno,avg(grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno having avg(grade)>=90;

        SNO AVG(GRADE)
    ---------- ----------
  94.3333333
  90.6666667

    5、查詢比平均成績至少比學(xué)號是3的平均成績高的學(xué)生學(xué)號以及平均分?jǐn)?shù)

    //having子句中可進(jìn)行比較和子查詢
    SQL> select sno,avg(grade) from sc
         group by sno
         having avg(grade) > (select avg(grade) from sc where sno=3);