上課紀錄: 6月 2015

2015年6月29日星期一

SQL練習：Date

PTT DataBase版 #1IN_QVHL

database:

id num date mark
-- ----- -------- ------------
1 a0001 9/1 訂單內容1
1 a0002 9/15 訂單內容2
2 a0003 9/17 訂單內容3
1 a0004 9/21 訂單內容4
3 a0005 9/24 訂單內容5
2 a0006 9/24 訂單內容6
1 a0007 10/1 訂單內容7

想要每個id 都列出data最新的一筆資料，列出結果如下

id num date mark
-- ----- -------- ------------
1 a0007 10/1 訂單內容7
2 a0006 9/24 訂單內容6
3 a0005 9/24 訂單內容5

--建立測試資料表
CREATE TABLE AA
 (
 id varchar(10) NULL,
 num nvarchar(20) NULL,
 n_date date NULL,
 mark varchar(10) NULL,
 )  ON [PRIMARY]

 insert into AA (id,num,n_date,mark) values ('1','a0001','2005-9-1','訂單內容1')
 insert into AA (id,num,n_date,mark) values ('1','a0002','2005-9-15','訂單內容2')
 insert into AA (id,num,n_date,mark) values ('2','a0003','2005-9-17','訂單內容3')
 insert into AA (id,num,n_date,mark) values ('1','a0004','2005-9-21','訂單內容4')
 insert into AA (id,num,n_date,mark) values ('3','a0005','2005-9-24','訂單內容5')
 insert into AA (id,num,n_date,mark) values ('2','a0006','2005-9-24','訂單內容6')
 insert into AA (id,num,n_date,mark) values ('1','a0007','2005-10-1','訂單內容7')

解法

先找出每個ID所對應的最新Date為AAA，inner join 原本的表(AA)

顯示的內容判斷AAA的日期=AA的日期後做排序

select AAA.id,num,AAA.n_date1 as n_date,mark
from (select AA.id,max(AA.n_date) as n_date1
      from AA
      group by id) AS AAA inner join AA ON AAA.id=AA.id
      where AAA.n_date1=AA.n_date
      order by id

SQL練習：ROW_NUMBER

PTT DataBase版 #1K___HRN

假設主表是"使用者"
另一張表是"對商品的評分"

使用者:

帳號名稱性別
A001 甲男
A002 乙女

對商品的評分:

帳號商品評分
A001 X商品 10
A001 Y商品 8
A002 X商品 9
A002 Z商品 7

希望可以產生像是如下的結果:

帳號名稱性別商品1 商品1評分商品2 商品2評分商品3...etc
A001 甲男 X商品 10 Y商品 8
A002 乙女 X商品 9 Z商品 7

每個人評分過的商品數量不一，但最多不超過四個
商品則可能有數十種

--建立測試資料表
CREATE TABLE nuser
 (
 ID varchar(10) NULL,
 name nvarchar(20) NULL,
 sex varchar(10) NULL,
 )  ON [PRIMARY]

 insert into nuser (ID,name,sex) values ('A001','甲','男')
 insert into nuser (ID,name,sex) values ('A002','乙','女')

CREATE TABLE sell
 (
 ID varchar(10) NULL,
 pro nvarchar(20) NULL,
 score int NULL,
 )  ON [PRIMARY]

 insert into sell (ID,pro,score) values ('A001','X商品',10)
 insert into sell (ID,pro,score) values ('A001','Y商品',8)
 insert into sell (ID,pro,score) values ('A002','X商品',9)
 insert into sell (ID,pro,score) values ('A002','Z商品',7)

解法

將商品評分表(sell)依各使用者(id)分類並且將商品排序(order by pro)

1	row_number() over(partition by sell.id order by sell.pro)

將各使用者排名第 1 的商品拉出來做一張表為TA

select * 
from ( select sell.*,
       row_number() over(partition by sell.id order by sell.pro) as rownum1
       from sell) AS A
       where rownum1=1

將各使用者排名第 2 的商品拉出來做一張表為TB

select * 
from ( select sell.*,
       row_number() over(partition by sell.id order by sell.pro) as rownum2
       from sell) AS B
       where rownum2=2

將TB、TA併入使用者表(nuser)，完整如下

select nuser.*,TAB.pro1,TAB.sc1,TAB.pro2,TAB.sc2
from nuser left outer join (
select TB.ID,TA.pro AS pro1,TA.score as sc1,TB.pro as pro2,TB.score as sc2 --將TB併入TA
from ( select * 
       from ( select sell.*, --找出ROW_NUMBER排名1的第1樣商品 AS TA
                     row_number() over(partition by sell.id order by sell.pro) as rownum1
              from sell) AS A
       where rownum1=1) as TA left outer join 
             ( select * 
        from ( select sell.*, --找出ROW_NUMBER排名2的第2樣商品 AS TB
                             row_number() over(partition by sell.id order by sell.pro) as rownum2
                 from sell) AS B
                      where rownum2=2) AS TB ON TA.ID=TB.ID
)AS TAB on TAB.ID=nuser.ID

SQL練習：CASE (2)

PTT DataBase版 #1HwemKch

Table A

類別名稱閱讀狀態我的最愛
---------------------------------
小說笑傲江湖 N Y
小說鹿鼎記 Y N
小說碧血劍 Y N
小說書劍恩仇錄 Y N

轉為

Table B

類別總筆數已閱讀完筆數喜愛筆數
--------------------------------------------
小說 4 3 1

--建立測試資料表
CREATE TABLE A
 (
 category varchar(10) NULL,
 name nvarchar(20) NULL,
 reading varchar(10) NULL,
 mylove varchar(10) NULL
 )  ON [PRIMARY]

 insert into A (category,name,reading,mylove) values ('小說','笑傲江湖','N','Y')
 insert into A (category,name,reading,mylove) values ('小說','鹿鼎記','Y','N')
 insert into A (category,name,reading,mylove) values ('小說','碧血劍','Y','N')
 insert into A (category,name,reading,mylove) values ('小說','書劍恩仇錄','Y','N')

解法

利用case判斷Y進行加總

select category as 類別,
 COUNT(*) as 總筆數,
 sum ( case when reading='Y' then 1 else 0 end) as 已閱讀完筆數, --若reading=Y回傳1，將所有值加總
 sum ( case when mylove='Y' then 1 else 0 end) as 已閱讀完筆數 --若mylove=Y回傳1，將所有值加總
from A
group by category

2015年6月28日星期日

SQL練習：CASE

PTT DataBase版 #192AKCJG

table:
name A B
1 1 3
2 3 3
3 2 2
4 1 4
5 5 2
6 5 4
7 3 2

假設原始資料為上表
我想將找出A跟B欄位值相同的為一群組
根據A,B欄位值的大小~以群組由大至小排序的即結果希望如下~

result table:(--# 數字相同帶表同群組)
name A B
6 5 4 -- 5
5 5 2 -- 5
4 1 4 -- 4
2 3 3 -- 3
7 3 2 -- 3
1 1 3 -- 3
3 2 2 -- 2

--建立測試資料表
CREATE TABLE AA
	(
	NAME nchar(10) NULL,
	A int NULL,
	B int NULL
	)  ON [PRIMARY]
	
	insert into AA (NAME,A,B) values ('1',1,3)
	insert into AA (NAME,A,B) values ('2',3,3)
	insert into AA (NAME,A,B) values ('3',2,2)
	insert into AA (NAME,A,B) values ('4',1,4)
	insert into AA (NAME,A,B) values ('5',5,2)
	insert into AA (NAME,A,B) values ('6',5,4)
	insert into AA (NAME,A,B) values ('7',3,2)

解法

1
2
3

select *
from AA
order by (case when A>B then A else B end) desc ,B desc

SQL練習：JOIN & Sub-Query (3)

PTT DataBase版 JOIN問題#1EdymxB7

想將3個資料表合併為一個

3個資料表的欄位都相同是由where條件不同而成

Table A
--------------
year id mea
100 001 10
100 002 20
99 006 15
99 007 21
98 008 10

Table B

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

year id mea

99 003 10

99 004 20

99 005 15

98 009 23

Table C

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

year id mea

98 010 12

想合併計算下面這個表單

count 為count(id) amea為SUM(mea)

Table ABC

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

A B C

year count amea count amea count amea

100 2 30 - - - -

99 2 36 3 45 - -

98 1 10 1 23 1 12

--建立測試資料表
CREATE TABLE A
	(
	year nchar(10) NULL,
	id nchar(10) NULL,
	mea int NULL
	)  ON [PRIMARY]
	
	insert into A (year,id,mea) values ('100','001',10)
	insert into A (year,id,mea) values ('100','002',20)
	insert into A (year,id,mea) values ('99','006',15)
	insert into A (year,id,mea) values ('99','007',21)
	insert into A (year,id,mea) values ('98','008',10)

CREATE TABLE B
	(
	year nchar(10) NULL,
	id nchar(10) NULL,
	mea int NULL
	)  ON [PRIMARY]
	
	insert into B (year,id,mea) values ('99','003',10)
	insert into B (year,id,mea) values ('99','004',20)
	insert into B (year,id,mea) values ('99','005',15)
	insert into B (year,id,mea) values ('98','009',23)
	
CREATE TABLE C
	(
	year nchar(10) NULL,
	id nchar(10) NULL,
	mea int NULL
	)  ON [PRIMARY]
	
	insert into C (year,id,mea) values ('98','010',12)

解法

將A、B、C表依年份進行GROUP，COUNT(id)，SUM(mea)，分別為TA、TB、TC

之後將TC併入TB併入TA再依年份排序

select TA.*,TB.count,TB.amea,TC.count,TC.amea

from ( select A.year,count(id) as count,sum(mea) as amea	--group A
	   from A
	   group by A.year) as TA 
	   left outer join ( select B.year,count(id) as count,sum(mea) as amea	--group B
						 from B
						 group by B.year) as TB  ON TA.year = TB.year
	   left outer join ( select C.year,count(id) as count,sum(mea) as amea	--group C
						 from C
						 group by C.year) as TC ON TA.year = TC.year

order by CONVERT(INT,TA.year) desc

SQL練習：JOIN & Sub-Query (2)

PTT DataBase版 JOIN問題#1Hcvzw_T

手中有三個表格，分別叫 A, B, C

其中資料是

A: B: C:
NAME_ID Name AGE_ID AGE NAME_ID AGE_ID
N1 Air A2 20 N2 A2
N2 Daisy A3 30

想合併三個表格變成輸出:

NAME_ID NAME AGE_ID AGE
N1 Air NULL NULL
N2 Daisy A2 20
NULL NULL A3 30

--建立測試資料表
CREATE TABLE TA
	(
	NAME_ID nchar(10) NULL,
	NAME nchar(10) NULL
	)  ON [PRIMARY]
	
	insert into TA (NAME_ID,NAME) values ('N1','Air')
	insert into TA (NAME_ID,NAME) values ('N2','Daisy')

CREATE TABLE TB
	(
	AGE_ID nchar(10) NULL,
	AGE INT NULL
	)  ON [PRIMARY]
	
	insert into TB (AGE_ID,AGE) values ('A2',20)
	insert into TB (AGE_ID,AGE) values ('A3',30)

CREATE TABLE TC
	(
	NAME_ID nchar(10) NULL,
	AGE_ID nchar(10) NULL
	)  ON [PRIMARY]
	
	insert into TC (NAME_ID,AGE_ID) values ('N2','A2')

解法

TA和TC完全外部合併為 A，再將A和TB再次外部合併為 B

1
2
3

select TA.*,B.*
from (TA full outer join TC AS A ON TA.NAME_ID=A.NAME_ID)	--TA和TC兩個表先外部合併 AS A
	 full outer join TB AS B ON A.AGE_ID=B.AGE_ID		--A和TB兩個表在外部合併 AS B

SQL練習：Pivot (2)

PTT DataBase版 JOIN問題#1IYlkmn

今天有一張資料表是長這樣的

國家年度月份日期台幣價格買入賣出

國家這一欄可能會有很多國家的資料,例如大陸美國英國加拿大

最後要的資料表是長這樣

年月美國英國大陸加拿大 .....

一次查詢就是找出某一年月各個國家的平均價格，然後把它組合起來畫統計圖

--創建測試資料表
CREATE TABLE AA
 (
 COUNTRY varchar(10) NULL,
 DATE date NULL,
 Price money NULL,
 )  ON [PRIMARY]

 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-26',30.55)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-25',30.56)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-24',30.54)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-28',30.44)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-27',30.45)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-26',30.55)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-27',30.44)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-26',30.98)
 insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-25',30.71)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-26',47.63)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-25',47.68)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-24',47.68)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-28',47.61)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-27',47.68)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-26',47.64)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-27',47.65)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-26',47.60)
 insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-25',47.65)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-26',24.76)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-25',24.78)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-24',24.79)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-28',24.70)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-27',24.75)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-26',24.76)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-27',24.75)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-26',24.70)
 insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-25',24.76)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-26',22.45)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-25',22.41)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-24',22.42)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-28',22.43)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-27',22.48)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-26',22.47)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-27',22.43)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-26',22.41)
 insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-25',22.43)

解法

先取出年月進行Group，再使用pivot進行樞紐

select *
From(
select CONVERT(varchar,DATEPART(yy,DATE))+REPLICATE('0',2-LEN(CONVERT(varchar,Datepart(mm,DATE))))+CONVERT(varchar,DATEPART(mm,DATE)) as 年月,
  COUNTRY,
  Price
from AA
Group by CONVERT(varchar,DATEPART(yy,DATE))+REPLICATE('0',2-LEN(CONVERT(varchar,Datepart(mm,DATE))))+CONVERT(varchar,DATEPART(mm,DATE)),
   COUNTRY,Price) AS PRV --取得年月進行Group

pivot ( AVG (Price) FOR COUNTRY IN ([美國],[英國],[加拿大],[新加坡])) AS PV

顯示結果

SQL練習：JOIN & Sub-Query & ISNULL

PTT DataBase版 JOIN問題#1KEwsOmC

把第二張的left join 給第一張，第三張是想要的結果

--創建測試資料表
CREATE TABLE CUSTNO_MONEY
	(
	CUSTNO varchar(10) NULL,
	BLOCNO varchar(10) NULL,
	MONEY int NULL
	)  ON [PRIMARY]

	insert into CUSTNO_MONEY(CUSTNO,BLOCNO,MONEY) values ('01','Z1',1000)
	insert into CUSTNO_MONEY(CUSTNO,BLOCNO,MONEY) values ('02','Z2',2000)
	insert into CUSTNO_MONEY(BLOCNO,MONEY) values ('Z3',3000)
	insert into CUSTNO_MONEY(BLOCNO,MONEY) values ('Z4',4000)
	insert into CUSTNO_MONEY(CUSTNO,MONEY) values ('05',5000)

CREATE TABLE CUSTNO_EMP
	(
	CUSTNO varchar(10) NULL,
	BLOCNO varchar(10) NULL,
	EMPCNAME varchar(10) NULL
	)  ON [PRIMARY]

	insert into CUSTNO_EMP(CUSTNO,BLOCNO,EMPCNAME) values ('01','Z1','N1')
	insert into CUSTNO_EMP(CUSTNO,BLOCNO,EMPCNAME) values ('02','Z2','N2')
	insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z3','N3')
	insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z4','N4')
	insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z4','N5')
	insert into CUSTNO_EMP(CUSTNO,EMPCNAME) values ('05','N6')

解法

根據CUSTNO將第二張表併入第一張AS B1

根據BLOCNO將第二張表併入B1 AS B2

SELECT時判斷EMPCNAME欄位是否為NULL

--使用ISNULL
SELECT CUSTNO_MONEY.*,ISNULL(B1.EMPCNAME,B2.EMPCNAME) AS EMPCNAME	--判斷B1.EMPCNAME是否為NULL，若為NULL用B2.EMPCNAME替換
FROM CUSTNO_MONEY
LEFT OUTER JOIN CUSTNO_EMP AS B1 ON CUSTNO_MONEY.CUSTNO = B1.CUSTNO	--將第二張表併入第一張AS B1，Key：CUSTNO
LEFT OUTER JOIN CUSTNO_EMP AS B2 ON CUSTNO_MONEY.BLOCNO = B2.BLOCNO	--將第二張表併入第一張AS B2，Key：BLOCNO

--一般查詢
select CUSTNO_EMP.CUSTNO,CUSTNO_EMP.BLOCNO,MONEY,EMPCNAME
from CUSTNO_EMP 
	 left outer join CUSTNO_MONEY 
	 ON CUSTNO_EMP.CUSTNO=CUSTNO_MONEY.CUSTNO OR CUSTNO_EMP.BLOCNO=CUSTNO_MONEY.BLOCNO

訂閱：文章 (Atom)

2015年6月29日 星期一

2015年6月28日 星期日

2015年6月29日星期一

2015年6月28日星期日