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

Question

表的结构非常简单，只有两列，ID跟CID，示例数据如下：

ID          CID
----------- -----------
0           1
0           2
0           3
1           1
1           2
1           3
2           2
2           3
3           1
3           2
3           3
4           1
4           2
4           3
5           1
5           3
6           1
6           2

我现在要查询的是CID的值中含有1和2的ID，如上面的数据中查询出来的结果应该是：

0、1、3、4、6

且要分页查询。

测试表及数据SQL：

--create test table
create table TestA(
    ID int not null,
    CID int not null,
    Primary Key(ID,CID)
);
GO


--insert into test data
declare @count int
set @count=0
while @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;
    end
Go

测试数据有13万，例如每页30条数据，查询第1334页，我的SQL语句及结果如下：

-- my method
set statistics io on
select top 30 ID from TestA
where (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 ID
having count(ID)>1
order by ID;
GO
/*
ID
-----------
39990
39991
39992
39993
39994
39995
39996
39997
39998
39999

(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 t1where t1.rowNo between 133 * 30 and 134 * 30   没有环境不知道性能如何！