首页 新闻 搜索 专区 学院

请教高效的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.
*/

求更高效的方案。

artwl的主页 artwl | 专家六级 | 园豆:16526
提问于:2012-03-02 15:22
< >
分享
最佳答案
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
写代码的小2B | 老鸟四级 |园豆:4346 | 2012-03-02 18:05
其他回答(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 )

imap | 园豆:232 (菜鸟二级) | 2012-03-02 15:49

你的代码执行结果:

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这个你这里没有啊

支持(0) 反对(0) artwl | 园豆:16526 (专家六级) | 2012-03-02 15:57

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

支持(0) 反对(0) imap | 园豆:232 (菜鸟二级) | 2012-03-03 11:59
0

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

Jolan | 园豆:171 (初学一级) | 2012-03-02 16:24

嗯,是的

支持(0) 反对(0) artwl | 园豆:16526 (专家六级) | 2012-03-02 16:36
0

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

小材小用 | 园豆:639 (小虾三级) | 2012-03-02 17:12

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

支持(0) 反对(0) artwl | 园豆:16526 (专家六级) | 2012-03-02 17:17

@artwl: 

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

结果为:3000

支持(0) 反对(0) 小材小用 | 园豆:639 (小虾三级) | 2012-03-02 18:01

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

支持(0) 反对(0) artwl | 园豆:16526 (专家六级) | 2012-03-02 18:17
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
小小刀 | 园豆:1991 (小虾三级) | 2012-03-03 00:19
1
select id from TestA s where s.coid in(1,2)
group by id having(count(coid))>=2

试试这个,分页我就没加上,你自己加上就行了。

收获园豆:20
webaspx | 园豆:1975 (小虾三级) | 2012-03-06 14:47
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册