萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> sql中的 where 、group by 和 having 用法解析

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

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

這篇文章主要介紹了sql中的 where 、group by 和 having 用法解析,需要的朋友可以參考下

廢話不多說了,直接給大家貼代碼了,具體代碼如下所示:

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

--如果要用到group by 一般用到的就是“每這個字” 例如說明現在有一個這樣的表:每個部門有多少人 就要用到分組的技術

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

--將會出現錯誤
--選擇列表中的列 'BasicDepartment.DepartmentName' 無效,因為該列沒有包含在聚合函數或 GROUP BY 子句中。
  這就是我們需要注意的一點,如果在返回集字段中,這些字段
  要麼就要包含在Group By語句的後面,作為分組的依據;
  要麼就要被包含在聚合函數中,作為分組的依據;
--出現的錯誤詳解:咱們看看group by 的執行的過程,先執行select 的操作返回一個程序集,
--然後去執行分組的操作,這時候他將根據group by 後面的字段
--進行分組,並且將相同的字段並稱一列數據,如果group by 後面沒有這個字段的話就要分成好多的數據。
--但是分組就只能將相同的數據分成兩列數據,而一列中又只能放入一個字段,所以那些沒有進行分組的
--數據系統不知道將數據放入哪裡,所以就出現此錯誤
--目前一種分組情況只有一條記錄,一個數據格是無法放入多個數值的,
--所以這裡就需要通過一定的處理將這些多值的列轉化成單值,然後將其放在對應的
--數據格中,那麼完成這個步驟的就是聚合函數。這就是為什麼這些函數叫聚合函數(aggregate functions)了

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

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

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

having是分組(group by)後的篩選條件,分組後的數據組內再篩選
where則是在分組前篩選

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

having 子句限制的是組,而不是行
having 子句中的每一個元素也必須出現在select列表中。有些數據庫例外,如oracle

當同時含有 where 子句、group by 子句 、having 子句及聚集函數時,執行順序如下:
執行where子句查找符合條件的數據;
使用group by 子句對數據進行分組;對group by 子句形成的組運行聚集函數計算每一組的值;最後用having 子句去掉不符合條件的組

  ex:  顯示每個地區的總人口數和總面積.僅顯示那些面積超過1000000的地區。 
    SELECT region, SUM(population), SUM(area)
    FROM bbc
    GROUP BY region
    HAVING SUM(area)>1000000
    在這裡,我們不能用where來篩選超過1000000的地區,因為表中不存在這樣一條記錄。(where子句中不能使用聚集函數)
    相反,HAVING子句可以讓我們篩選成組後的各組數據

  ex:create TABLE Table1
    (
      ID int identity(1,1) primary key NOT NULL, 
      classid int,
      sex varchar(10),
      age int,
    ) 
    --添加測試多條數據
    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)
    ..........
    查詢每一個班級中年齡大於20,性別為男的人數至少為2個的情況
    select COUNT(age) as '>20歲人數',classid from Table1 where sex='男' group by classid having COUNT(age)>2

底下這個例子很好
    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

    這個表所描述的是4個學生對應每科學習成績的記錄,其中SNO(學生號)、PNO(課程名)、GRADE(成績)。

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

    //這是一個簡單的查詢,並沒有使用分組查詢

    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、顯示每個學生的成績在90分以上的各有多少門

    --進行分組顯示,並且按照where條件之後計數

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

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

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

    //進行分組顯示,並且按照where條件之後計數,在根據having子句篩選分組

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

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

    這個結果是我們想要的,它列出了具有評選三好學生資格的學生號,跟上一個例子比較之後,發現這是在分組後進行的子查詢。

    4、學校評選先進學生,要求平均成績大於90分的學生都有資格,並且語文課必須在95分以上,請列出有資格的學生

    //實際上,這個查詢先把語文大於95分的學生號提取出來,之後求平均值,分組顯示後根據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、查詢比平均成績至少比學號是3的平均成績高的學生學號以及平均分數

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

copyright © 萬盛學電腦網 all rights reserved