小雕雕的家

前言...

之前用Oracle 的時候使用 Row_Number() + PARTITION 後可以在外面加上 select * () where ....去過濾 partition 出來的東西

但是偶在 MS-SQL 試的結果會出現  接近 ')' 之處的語法不正確。

ex: 

select row_number() over(partition by a.id order by a.createdate desc) as rowno ,

a.id,a.starname,a.createdate,b.img from starwear a join starwear_detail b on a.id = b.sid 

出來的結果是

在 Oracle 可以這樣下

select * from (

select row_number() over(partition by a.id order by a.createdate desc) as rowno ,

a.id,a.starname,a.createdate,b.img from starwear a join starwear_detail b on a.id = b.sid 

) where rowno = 1

就會變成我要的結果

但在 MS-SQL 會變成這樣   接近 ')' 之處的語法不正確。

因為很少用....然後我完全不知道要怎麼搞

因此google 找到的結果是要用 MS-SQL 的 CTE  (common table expression)

參考藍星的其他大前輩是這樣搞的

with tmp as (

select row_number() over(partition by a.id order by a.createdate desc) as rowno ,

a.id,a.starname,a.createdate,b.img from starwear a join starwear_detail b on a.id = b.sid 

)

select * from tmp where rowno = 1

這是比較雜魚等級的應用,還可以利用遞迴方式剖樹狀結構的樣子

http://www.dotblogs.com.tw/dc690216/archive/2010/02/02/13440.aspx

然後 oracle 裡面好用的 limit 功能也可以做到

http://diary.tw/tim/42

話說mysql + oracle 裡的 limit 在分頁的時候真的很好用

以上一篇連結的大大舉的例子

查詢訂單資料表(orders), 依訂單金額由大而小排序, 取出第301到第400筆資料:

MySQL:
select orderid, orderdate, orderamount from orders order by orderamount desc limit 301, 400 

實在是非常的好用....

然後 CTE不能使用在以下的方式

• COMPUTE o COMPUTE BY

• ORDER BY (excepto cuando se especifica una cláusula TOP)

• INTO

• Cláusula OPTION con sugerencias de consulta

• FOR XML

• FOR BROWSE

MSDN : http://msdn.microsoft.com/es-es/library/ms175972.aspx

補充 : 原來是我白痴 = =

在SQL也是可以....但是要 + as 

select * from (

select row_number() over(partition by a.id order by a.createdate desc) as rowno ,

a.id,a.starname,a.createdate,b.img from starwear a join starwear_detail b on a.id = b.sid 

)  as xxx  where rowno = 1