请教高效的SQL Server同一列个包含多个值的分页查询方案

Question

表的结构非常简单，只有两列，ID跟CID，示例数据如下： ID CID----------- -----------0 10 20 31 11 21 32 22 33 13 23 34 14 24 35 15 36 16 2 我现在要查询的是CID的值中含有1和2的ID，如上面的数据中查询出来的结果应该是： 0、1、3、4、6 且要分页查询。 测试表及数据SQL： --create test tablecreate table TestA( ID int not null, CID int not null, Primary Key(ID,CID));GO--insert into test datadeclare @count intset @count=0while @count<50000 begin if @count<40000 begin insert into TestA values (@count,1); insert into TestA values (@count,2); insert into TestA values (@count,3); end else insert into TestA values (@count,2); set @count=@count+1; endGo 测试数据有13万，例如每页30条数据，查询第1334页，我的SQL语句及结果如下： -- my methodset statistics io onselect top 30 ID from TestAwhere (CID =1 or CID =2) and ID >(SELECT MAX(ID) FROM (select top (1333*30) id from TestA where CID=1 or CID=2 group by ID having count(ID)>1 order by ID)a)group by IDhaving count(ID)>1order by ID;GO/*ID-----------39990399913999239993399943999539996399973999839999(10 row(s) affected)Table 'TestA'. Scan count 2, logical reads 279, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/ 求更高效的方案。

Answer 1

SELECT ID FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROW,ID FROM
    (
        SELECT ID FROM TestA WHERE CID=1 GROUP BY ID
        UNION ALL
        SELECT ID FROM TestA WHERE CID=2 GROUP BY ID
    ) AS A GROUP BY ID HAVING COUNT(ID)>1
) AS A WHERE ROW BETWEEN 1333*30 AND 1334*30

我感觉和你的差不多。

Answer 2

--- 下面这个效率就不错啊，你试试

with t1
as ( select row_number() over ( order by t.ID asc ) as rowNo, CID,
ID
from TestA t
)
select *
from t1
where t1.rowNo between 133 * 30 and ( 133 * 30 + 30 )

Answer 3

@artwl: 我没加查询条件，修改一下试试看可以不，抱歉哈。

Answer 4

想问一下，你的ID是不是不能重复呀

Answer 5

嗯，是的

Answer 6

你不是要每页30条吗？你怎么只有10条？

Answer 7

第1334页是最后一页，只有10条数据

Answer 8

@artwl: 

 select COUNT(1)/30.0 from testa where (cid = 1
           OR cid = 2)

结果为：3000

Answer 9

@小材小用: 不是OR的关系

Answer 10

select * from (
select id,row_number() over ( order by t.ID asc ) as rowno from 
(select a.id from TestA a inner join TestA  b on a.id=b.id and a.cid =1 and b.cid =2) as t) as t1
where t1.rowNo between 133 * 30 and 134 * 30

 

没有环境不知道性能如何！