使用 group by 或是利用 partition by來取得資料

目前有一個需求，希望可以取得某一段區間的訂單資料，並用公司、客戶、產品型號、幣別、月份作 Group By，來計算產品數量，還有在這一期間內該產品的最新單價作為下年度的預估單價，而最新單價的部份用 Group By的方式如下：


select  company,curr_code,cust_code, prod_num,max(price)
 from ( select a.Fa02 'company'
 ,isnull(a.Fa13, 'NT$') 'curr_code'
 ,a.Fa061 'cust_code'
 ,c.Fb04 'prod_num'
 ,c.Fb23 'order_lline_date'
 , isnull(c.Fb14,0) 'price'

from DATAWIN.dbo.kfm01 a
,DATAWIN.dbo.kfm02 c
where 1=1
and a.Fa01 = c.Fb01
and isnull(Fa124,'N') = 'N'
and LEFT(c.Fb23 ,6) >= '201301'
and LEFT(c.Fb23 ,6) <= '201312'

and c.Fb23 = (select max(Fb23) from  DATAWIN.dbo.kfm01 e,DATAWIN.dbo.kfm02 f
where 1=1
and e.Fa01 = f.Fb01
and isnull(e.Fa124,'N') = 'N'
and LEFT(f.Fb23 ,6) >= '201301'
and LEFT(f.Fb23 ,6) <= '201312'
and a.Fa02 = e.Fa02
and a.Fa13 = e.Fa13
and a.Fa061 = e.Fa061
and c.Fb04 = f.Fb04
             )
 ) a
 group by company,curr_code,cust_code, prod_num ;

上面的作法應該是大家常使用的，不過還有另一種作法是使用 ROW_NUMBER() OVER (PARTITION BY ORDER BY)的方式來達成，作法如下，寫法比 Group By還簡單，可以參考看看。

select * from (

select a.Fa02 'company'

 ,isnull(a.Fa13, 'NT$') 'curr_code'  

 ,a.Fa061 'cust_code'

 ,c.Fb04 'prod_num'

 ,c.Fb23 'order_lline_date' 

 , isnull(c.Fb14,0) 'price' 

 ,ROW_NUMBER() OVER  (PARTITION BY a.Fa02,isnull(a.Fa13, 'NT$'),a.Fa061,c.Fb04  ORDER BY c.Fb23 DESC) AS RankByDate

from DATAWIN.dbo.kfm01 a 

,DATAWIN.dbo.kfm02 c

where 1=1 

and a.Fa01 = c.Fb01

and isnull(Fa124,'N') = 'N'

and LEFT(c.Fb23 ,6) >= '201301' 

and LEFT(c.Fb23 ,6) <= '201312'

and isnull(c.Fb14,0) <> 0

) a where a.RankByDate =1

order by company,curr_code,cust_code, prod_num,price