sqlserver2000复杂语句分页

倒霉得很,公司的数据库是2000的,rownumber用不了,WITH AS用不了,查询条件还慢复杂,想了蛮久,没啥办法了,求助下。下面是最简单的一条sql:
SELECT a.autoid,a.callerid,a.doorid,a.cardday,a.eventtype
FROM Cr_CardEvent a, Cr_Caller_BasicInfo b
WHERE a.callerid = b.callerid AND b.callertype = 1
AND a.cardday BETWEEN '2011-09-01' AND '2011-09-02'
抛砖引玉...
下面这条是上面的小小升级版
SELECT c.autoid,c.callerid,c.cardid,c.doorid,c.cardday,c.eventtype,d.doorid AS max_doorId,d.eventType AS max_eventSort,d.cardday AS max_cardDay
FROM (SELECT * FROM cr_cardevent a WHERE EXISTS (SELECT 1 FROM(SELECT MIN(cardday)min_time ,callerid FROM cr_cardevent c,Com_EmpCard card
WHERE cardday BETWEEN '2014-09-01' AND '2014-10-01' AND c.cardid=card.cardid AND EXISTS
(SELECT 1 FROM Finger_Data fingure where fingure.cardid=card.cardid) GROUP BY convert(varchar(10),cardday,121), callerid)b
WHERE a.cardday=b.min_time AND a.callerid=b.callerid ))c ,(SELECT * FROM cr_cardevent a WHERE EXISTS
(SELECT 1 FROM(SELECT max(cardday)min_time ,callerid
FROM cr_cardevent c,Com_EmpCard card where cardday BETWEEN '2014-09-01' AND '2014-10-01' AND c.cardid=card.cardid GROUP BY convert(varchar(10),cardday,121), callerid)b WHERE a.cardday=b.min_time AND a.callerid=b.callerid))d,cr_caller_basicinfo e
WHERE c.callerid = d.callerid AND c.callerid = e.callerid AND e.callertype=1
AND 0=DATEDIFF(DAY, convert(varchar(10),c.cardday,121),convert(varchar(10),d.cardday,121))

0

5个回答

最后用的游标解决问题。在这种rownumber函数和CTE没法用并且限制颇多(权限)的sqlserver2000,只能用了游标去分页

0

同样不懂,不懂,不懂

0

图片说明一笑而过,不是很懂

0

你可以把你需要查询的数据建立成表视图,再进行分页

0

子查询有点多,看能不能优化成连接查询,速度就会快点。同时建立索引

0
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!