当前位置: 代码迷 >> Sql Server >> 很坑爹的sql 排序有关问题
  详细解决方案

很坑爹的sql 排序有关问题

热度:10   发布时间:2016-04-27 13:40:19.0
很坑爹的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。
  相关解决方案