实现SQL2005中的row_number()函数

 

可以通过以下sql语句来实现row_number（）函数相同的效果

 

 

SELECT * FROM (SELECT (SELECT COUNT(*) FROM snd s2  WHERE s2.id <= s1.id) as rownum, * FROM snd AS s1) AS TMP

但是必须要id不重复，如果你能找到你的表中的几个字段合起来永远不会重复，就能行。

 

运用颠倒算法：

 strSql.Append("SELECT g.id, g.sid, g.active_date, g.sn, h.reg_key, h.active_code FROM ");
            strSql.Append(" (SELECT  f.id, f.sid, f.active_date, sn.sn FROM ");

　　　　　　 // maximumRows每页显示行数
            strSql.AppendFormat("(SELECT     TOP 100 PERCENT id, sid, active_date FROM (SELECT TOP {0} ", maximumRows);
            strSql.Append("id, sid, active_date FROM (SELECT TOP 100 PERCENT id, sid, active_date FROM ");

　　   　　 //startRowIndex 起始行
            strSql.AppendFormat("(SELECT TOP {0}  id, sid,active_date FROM ", startRowIndex + maximumRows);
            strSql.Append("(SELECT  TOP 100 PERCENT b.id, b.sid,b.active_date,b.reg_key,b.active_code FROM ");
            strSql.Append(" table AS b  ORDER BY b.sid,active_date desc) AS c ");
            strSql.Append("where (1=1)");
            if(!string.IsNullOrEmpty(strReg))
            {
               strSql.AppendFormat(" AND reg_key = '{0}' ", strReg);
            }

 

            if (!string.IsNullOrEmpty(strActiveCode))
            {
                strSql.AppendFormat(" AND active_code = '{0}' ", strActiveCode);
            }
            if (!string.IsNullOrEmpty(strSN))
            {
                strSql.AppendFormat(" AND sid = (select id from s2 where sn='{0}')  ", strSN);
            }
            strSql.Append(") AS d ORDER BY sid DESC,active_date) AS e_1) AS e ORDER BY sid,active_date desc) AS f ");
            strSql.Append("INNER JOIN sn ON f.sid = sn.id) AS g INNER JOIN sn_reg_activecode_relation AS h ON g.id = h.id");