SQL Server 2008, 用到了分页查询,语句如下
SELECT * FROM (SELECT ROW_NUMBER() OVER( order by XX desc) AS ROWID, DISTINCT *,...... WHERE ......) AS TEMP1 WHERE (ROWID >= 0) AND (ROWID <= 25)
由于ROWID的存在,导致DISTINCT 失效,无法过滤重复的数据.因为ROWID本身就是一个不重复的数据,但是如果去掉ROWID的话,我又没法进行分页查询,也就是说无法执行 (ROWID >= 0) AND (ROWID <= 25)这个条件.
如果我使用with as ,如下:
WITH KK AS(SELECT DISTINCT ...) SELECT ROW_NUMBER() OVER( order by id desc) AS ROWID, * FROM KK WHERE (ROWID >= 0) AND (ROWID <= 25)
但这个时候又提示说ROWID 无效,怎么办?
------解决思路----------------------
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER( order by XX desc) AS ROWID
FROM (SELECT DISTINCT *,...... WHERE ......
) AS TEMP1
) TEMP2
WHERE (ROWID >= 0) AND (ROWID <= 25)
------解决思路----------------------
select * from
(
SELECT
ROW_NUMBER() OVER( order by XX desc) AS ROWID,
*
FROM
(SELECT , DISTINCT * WHERE ......) AS TEMP1 ) as a
WHERE
(ROWID >= 0) AND (ROWID <= 25)