2015年6月29日 星期一

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

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


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
--建立測試資料表
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
1
2
3
4
5
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

1
2
3
4
5
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)，完整如下


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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

張貼者： 於
標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)