count,group by,having(SQL)

---所有書籍價格的統計

select sum(price)總價,avg(price)均價,max(price)最高價,min(price)最低價

from titles

---統計where條件的記錄
---business類型書籍價格的統計
select sum(price)總價,avg(price)均價,max(price)最高價,min(price)最低價
from titles where type='business'

--count返回記錄的條數
--返回作者共來自幾個州
select count (distinct state)州數 from authors 

select count(au_id) from authors
--返回表的記錄的條數
select count(*) from authors

select * from titles

--type類型的記錄條數
select count(distinct type) from titles

select count(title_id) from titles

--group by
--返回各個類別的書籍的統計
select type, sum(price) 總價,avg(price) 均價,max(price) 最高價,min(price) 最低價,
count(*) 條數 from titles group by type

--返回各個出版社分別出版書籍的數量並排序(降序)
select * from titles
select pub_id, count(*) 數量 from titles group by pub_id order by 數量 desc

---1389出版社出版的書籍數量
select * from titles
select count(*) 數量 from titles where pub_id=1389

--對type,pub_id進行分組統計
select count(*) 數量,type,pub_id from titles group by type,pub_id 
order by 數量 desc

--having篩選組
--返回類別的均價>15的書籍的統計
select avg(price)均價,type from titles group by type having avg(price)>15
--注:先求平均值,再求均價>15的記錄.

select avg(price) 均價,type from titles
where price>15 group by type
--注:先求價格>15的記錄,再根據類別求其價格>15的均價.

--要返回平均價格在13到18之間的圖書分類
select avg(price) 均價,type from titles group by type 
having avg(price) between 13 and 18

--返回出版書籍的數量>=6的出版社編號
select * from titles

select count(*) 數量,pub_id from titles
group by pub_id having count(*)>=6

--返回作者人數最多的state名字
select * from authors

select top 1 state,count(*)數量 from authors group by state
order by count(*) desc

--返回business,mod_cook這兩個類別的統計資訊
select * from titles

select type,sum(price) 總價,avg(price) 均價,max(price) 最高價,min(price) 最低價
from titles where type in('business','mod_cook') group by type
--注:先根據where條件將business,mod_cook類別的書籍選出,再進行統計.

select type,sum(price) 總價,avg(price) 均價,max(price) 最高價,min(price) 最低價
from titles group by type having type in('business','mod_cook')
--注:先進行統計,再根據where條件將business,mod_cook類別的書籍選出.

 

--------------------------------------------------

如：  查詢學生的平局成績大於60的  SELECT FSno,AVG(FGrade) AS FAvgGrade  FROM TStudent  GROUP BY FSno  HAVING AVG(FGrade) > 60 等價於  SELECT *  FROM   (    SELECT FSno,AVG(FGrade) AS FAvgGrade    FROM TStudent    GROUP BY FSno  ) AS TWHERE FAvgGrade>60:having效能更高，代碼更簡潔關於能夠用於條件式篩選的 ON:連接(join)篩選WHERE: 一般條件式篩選HAVING: 分組後的條件式篩選

--------------------------------------------------

如果一個列 沒有出現在 group by 子句中,那麼該列 就不能直接出現在select 後面 或者 having 子句後面,必須加上彙總函式才行.例如：select min(年齡) ,性別 from 職工表 group by 性別---若，年齡 沒有出現在group by 子句中，必須加彙總函式才能寫在select 的後面。