浅谈Sql Server 2005 中的ROW_NUMBER()
最近因为业务需要,使用到了Sql Server 2005.其中要求生成行号,以前用Oracle的时候这也是个小问题的,在SELECT是直接使用ROWNUM 就可以得到。 Sql 2005 给出了一个新的方法来得到行号——ROW_NUMBER()配合OVER实现,例如:
SELECT ROW_NUMBER() OVER(ORDER BY 列名) FROM TABLE.不过随之而来的出了一个问题,这样会把 重复的行给显示出来,即使你使用了DISTINCT也一样。这个问题最终也只是通过在外层添加行号得以解决。
SELECT ROW_NUMBER() OVER(ORDER BY TEMP1.ITEM_MANAGE) AS SEQ
,*
FROM(
SELECT DISTINCT
TEMP.ITEM_MANAGE
,TEMP.SUPLI_DATE
,TEMP.ITEMCODE
,TEMP.COLOR
,TEMP.SIZE
,TEMP.ITEMNAME
,TEMP.CLASS
... ...
He deals the cards as a meditation
And those he plays never suspect
He doesn't play for the money he wins
He doesn't play for the respect
He deals the cards to find the answer
And those he plays never suspect
He doesn't play for the money he wins
He doesn't play for the respect
He deals the cards to find the answer
posted on 2008-12-11 13:09 Leon Reno 阅读(1084) 评论(0) 编辑 收藏 举报