请教高效的SQL Server同一列个包含多个值的分页查询方案
0
悬赏园豆：100
[已解决问题]
解决于 2012-03-14 09:50
表的结构非常简单，只有两列，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.
*/
求更高效的方案。
最佳答案
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
我感觉和你的差不多。
收获园豆：40
其他回答(5)
0
--- 下面这个效率就不错啊，你试试
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 )
你的代码执行结果：
rowNo CID ID
-------------------- ----------- -----------
3990 3 1329
3991 1 1330
3992 2 1330
3993 3 1330
3994 1 1331
3995 2 1331
3996 3 1331
3997 1 1332
3998 2 1332
3999 3 1332
4000 1 1333
4001 2 1333
4002 3 1333
4003 1 1334
4004 2 1334
4005 3 1334
4006 1 1335
4007 2 1335
4008 3 1335
4009 1 1336
4010 2 1336
4011 3 1336
4012 1 1337
4013 2 1337
4014 3 1337
4015 1 1338
4016 2 1338
4017 3 1338
4018 1 1339
4019 2 1339
4020 3 1339
(31 row(s) affected)
Table 'TestA'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
但这个不对啊，结果应该只有10条才对，CID的值中含有1和2的ID这个你这里没有啊
@artwl: 我没加查询条件，修改一下试试看可以不，抱歉哈。
0
想问一下，你的ID是不是不能重复呀
嗯，是的
0
你不是要每页30条吗？你怎么只有10条？
第1334页是最后一页，只有10条数据
@artwl:
select COUNT(1)/30.0 from testa where (cid = 1
OR cid = 2)
结果为：3000
@小材小用: 不是OR的关系
1
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
没有环境不知道性能如何！
收获园豆：40
1
select id from TestA s where s.coid in(1,2)
group by id having(count(coid))>=2
试试这个，分页我就没加上，你自己加上就行了。
收获园豆：20
