很坑爹的sql 排序有关问题-MSSQL教程-爱易网页

很坑爹的sql 排序有关问题

日期：2014-05-18　浏览次数：20552 次

很坑爹的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。

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

很坑爹的sql 排序有关问题

相关资料更多>

推荐阅读更多>