u010603019
若邪
采纳率83.3%
2015-10-28 06:32

sqlserver2000复杂语句分页

3
已采纳

倒霉得很,公司的数据库是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))

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

5条回答

  • u010603019 若邪 6年前

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

    点赞 评论 复制链接分享
  • Evankaka Evankaka 6年前

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

    点赞 评论 复制链接分享
  • lxf276541049 耕地的蚂蚁 6年前

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

    点赞 评论 复制链接分享
  • MIZOOZHOU MIZOOZHOU 6年前

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

    点赞 评论 复制链接分享
  • HyMw825 MoSir526 6年前

    同样不懂,不懂,不懂

    点赞 评论 复制链接分享

相关推荐