很坑爹的sql 排序有关问题

很坑爹的sql 排序问题
原始代码 排序没问题

SQL code

select b.Knowledge,c.LectureTitle,a.StudyNum ,(select COUNT(*) from studyDBNew.dbo.tblUserStudyRecord as r         where r.UserId = 'test' and r.LectureGuid = c.LectureGuid) as userNum ,(select LoreTitle+' ' from studyDBNew.dbo.tblLectureLore as i      left join studyDBNew.dbo.tblCourseLore as ii on i.LoreId = ii.CourseLoreId       where c.LectureGuid = i.LectureGuid for XML path('') ) as lore ,c.LectureContent,c.LectureId,a.aid,c.ToUrl2 from studyDBNew.dbo.tblCourseWare as a left join studyDBNew.dbo.tblKnowledge as b on a.CourseWareGuid = b.CourseWareGuid left join studyDBNew.dbo.tblLectures as c on b.KnowledgeGuid = c.KnowledgeGuid where a.CourseWareId =628 and isnull(LectureId,'')<>'' order by b.Sort,c.Sort

ROW_NUMBER() 后排序完全变了

SQL code

SELECT * FROM (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY b.Sort,c.sort) Row,b.Knowledge,c.LectureTitle,a.StudyNum ,(select COUNT(*) from studyDBNew.dbo.tblUserStudyRecord as r        where r.UserId = 'test' and r.LectureGuid = c.LectureGuid) as userNum ,(select LoreTitle+' ' from studyDBNew.dbo.tblLectureLore as i     left join studyDBNew.dbo.tblCourseLore as ii on i.LoreId = ii.CourseLoreId       where c.LectureGuid = i.LectureGuid for XML path('') ) as lore ,c.LectureContent,c.LectureId,a.aid,c.Sort from studyDBNew.dbo.tblCourseWare as a left join studyDBNew.dbo.tblKnowledge as b on a.CourseWareGuid = b.CourseWareGuid left join studyDBNew.dbo.tblLectures as c on b.KnowledgeGuid = c.KnowledgeGuid  where a.CourseWareId =628 and isnull(LectureId,'')<>'' )  TMP WHERE Row>0 AND Row<=10

经过查找后发现 把 r.UserId = 'test' 去掉后 就正常了

SQL code

SELECT * FROM (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY b.Sort,c.sort) Row,b.Knowledge,c.LectureTitle,a.StudyNum ,(select COUNT(*) from studyDBNew.dbo.tblUserStudyRecord as r        where       -- r.UserId = 'test' and        r.LectureGuid = c.LectureGuid) as userNum ,(select LoreTitle+' ' from studyDBNew.dbo.tblLectureLore as i     left join studyDBNew.dbo.tblCourseLore as ii on i.LoreId = ii.CourseLoreId       where c.LectureGuid = i.LectureGuid for XML path('') ) as lore ,c.LectureContent,c.LectureId,a.aid,c.Sort from studyDBNew.dbo.tblCourseWare as a left join studyDBNew.dbo.tblKnowledge as b on a.CourseWareGuid = b.CourseWareGuid left join studyDBNew.dbo.tblLectures as c on b.KnowledgeGuid = c.KnowledgeGuid  where a.CourseWareId =628 and isnull(LectureId,'')<>'' )  TMP WHERE Row>0 AND Row<=10

请各位大神帮帮小弟解解惑啊！！！

[解决办法]

探讨

没人吗？自己先顶下！！！

[解决办法]
没有order by的行集是无序的，最后order by。

查看更多 下一篇