SQL第3堂課 聚合函數

紀錄一下Red第3次上課的內容，主要介紹聚合函數的應用和Group by的用法
參考資料

• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()
• GROUP BY

本週作業練習

練習1

請寫出一個SQL,從table User_List,計算員工IDL/DL於各星座之人數,顯示欄位如result

IDL/DL	星座	人數
DL	天瓶座	…
IDL	天瓶座	…

解答

select idl
,case month(birth) 
when 1 then '1月魔羯座' 
when 2 then '2月水瓶座'
when 3 then '3月雙魚座'
when 4 then '4月白羊座'
when 5 then '5月金牛座'
when 6 then '6月雙子座'
when 7 then '7月巨蟹座'
when 8 then '8月獅子座'
when 9 then '9月處女座'
when 10 then '10月天枰座'
when 11 then '11月天蠍座'
when 12 then '12月射手座'
end
as 星座
,count(idl) as 人數
from user_list
where active = 'Y' 
and idl in ('idl','dl')
group by idl,month(birth)
order by month(birth)

執行結果

IDL/DL	星座	人數
DL	1月魔羯座	2485
IDL	1月魔羯座	1344
DL	2月水瓶座	2516
IDL	2月水瓶座	987

...

練習2 
請寫出一個SQL,計算員工信託,X公司可能要付出的總補助費用,假設年資0~3年,補助0;  3~5年,補助1000 ;  5~10年補助5000;  10年以上,補助10000
Table : User_List,用come_date欄位判斷工作年資,IDL='IDL', active='Y', 顯示結果如result.

年資	總補助費用
0~3年
3~5年
5~10年
10年以上

解答

select 年資, case 年資
when '0~3年資'
then 人數*0
when '3~5年資'
then 人數*1000
when '5~10年資'
then 人數*5000
when '10年以上'
then 人數*10000
end as 總補助費用
from
(select case
when come_date > dateadd(year,-3,getdate())
then '0~3年資'
when come_date <= dateadd(year,-3,getdate())
and come_date > dateadd(year,-5,getdate())
then '3~5年資'
when come_date <= dateadd(year,-5,getdate())
and come_date > dateadd(year,-10,getdate())
then '5~10年資'
when come_date < dateadd(year,-10,getdate())
then '10年以上'
end as 年資,count(idl) as 人數
from user_list
where idl ='idl'
and active = 'Y'
group by case
when come_date > dateadd(year,-3,getdate())
then '0~3年資'
when come_date <= dateadd(year,-3,getdate())
and come_date > dateadd(year,-5,getdate())
then '3~5年資'
when come_date <= dateadd(year,-5,getdate())
and come_date > dateadd(year,-10,getdate())
then '5~10年資'
when come_date < dateadd(year,-10,getdate())
then '10年以上'
end) mytable

練習3 
題目: 寫一個SQL,使用MIN+Subquery,列出友達最資深的員工姓名(EMP_CNAME),生日(Birth), 到職日(Come_date),部門(dept_code),請填在result

EMP_CNAME	Dept_code	Come_date	Birth

 
解答

select emp_cname, dept_code,come_date,birth
from user_list
where come_date = (select MIN(come_date)
from user_list
where active = 'Y'
and come_date <> '1900/01/01')

練習4

寫出一個SQL,join table : order_dtl, product,找出2010年度,對公司營收貢獻最大的產品,列出如result的結果

order_dtl

order_no	product_ID	Order_Date	qty
1	a	2010/01/07	10
1	b	2010/03/05	20
2	a	2010/02/03	100
2	d	2010/01/08	200
3	c	2009/12/31	100

Product

product_ID	Product_name	unit_price
a	LCD panel	1000
b	mouse	50
c	keyboard	100
d	ipad	1000

result

Product_name	Amount

解答

select top(1) p.product_name as ProductName, SUM(o.qty*p.unit_price) as amount
from product p
inner join order_dtl o
on p.product_id = o.product_id
where year(o.order_date) = 2010
group by p.product_name

Product_name	Amount
ipad	200000