ROW_NUMBER分页，由子查询结果来排序

[scyyzgxh]

需求像是下面这样子的：
假设有个学生用户表，有个学生选课表，现在我想将学生数据列表分页显示出来，但同时也想将学生的选课门数显示出来，并且根据门数来desc，目前我尝试了以下两种写法如下：
法一：

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY (门数 desc) as row_number,
UserName,StudentID,
(select count(1) from Course C where C.StudentID=T.StudentID) as '门数' from Student
)
AS T
 WHERE row_number between 1 and 10

法二：

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY (select count(1) from Course C where C.StudentID=T.StudentID) desc) as row_number,
UserName,StudentID,
(select count(1) from Course C where C.StudentID=T.StudentID) as '门数' from Student
)
AS T
 WHERE row_number between 1 and 10

两个写法的差别是法二仅在OVER中写多一次子查询，结果是法一语法错误，找不到门数这个一句来排序，法二正常
但是法二有两个一样的子查询，总感觉不是很顺心，请问高手们这种还可以如何解决呢？
PS：之前有想过在Student表中添加统计列，或者再弄一个表来专门统计数目再left join使用，但总感觉不够灵活，假设我只想统计学生这个学期的科目数呢？假设我想统计学生全部选的化学课数呢？

[二月十六]

用left join才有问题，没有课的也会出来，你具体看一下课程表里的数据

[stelf]

sum( case when c.id is null then o else 1 end) 如果你觉得你那种结果是你想要的，数量可以用这种方式来计算。

引用 6 楼 scyyzgxh 的回复:

[quote=引用 4 楼 二月十六 的回复:] 试试这样，没测试

SELECT *
  FROM (   SELECT T.UserName,
                  T.StudentID,
                  COUNT(1) AS 门数,
                  ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) [row_number]
             FROM Student T
             JOIN Course C
               ON C.StudentID = T.StudentID
            GROUP BY T.UserName,
                     T.StudentID) a
 WHERE row_number BETWEEN 1 AND 10;

能运行，但似乎结果不太正确呢，它只有一行数据，应该是显示有选课的学生，但是没有选课的学生不显示了 将里面单独抽出来改为left join，可以显示其它学生的情况，但是门数不正确，没有选课的学生门数全部显示为1了 （里面有用户名重复的是我随便改的测试数据，实际上是不同用户名的，不要在意） [/quote]

[stelf]

简直是在猜结果

引用 7 楼 二月十六 的回复:

用left join才有问题，没有课的也会出来，你具体看一下课程表里的数据

[scyyzgxh]

引用 7 楼 二月十六 的回复:

用left join才有问题，没有课的也会出来，你具体看一下课程表里的数据

谢谢，我的需求是——想将学生数据列表分页显示出来，但同时也想将学生的选课门数显示出来，并且根据门数来desc
所以没有选课的学生，我也要显示的，没有的话，显示为0即可

[scyyzgxh]

谢谢

引用 8 楼 stelf 的回复:

sum( case when c.id is null then o else 1 end)

如果你觉得你那种结果是你想要的，数量可以用这种方式来计算。









[quote=引用 6 楼 scyyzgxh 的回复:]
[quote=引用 4 楼 二月十六 的回复:]
试试这样，没测试

SELECT *
  FROM (   SELECT T.UserName,
                  T.StudentID,
                  COUNT(1) AS 门数,
                  ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) [row_number]
             FROM Student T
             JOIN Course C
               ON C.StudentID = T.StudentID
            GROUP BY T.UserName,
                     T.StudentID) a
 WHERE row_number BETWEEN 1 AND 10;

能运行，但似乎结果不太正确呢，它只有一行数据，应该是显示有选课的学生，但是没有选课的学生不显示了
将里面单独抽出来改为left join，可以显示其它学生的情况，但是门数不正确，没有选课的学生门数全部显示为1了
（里面有用户名重复的是我随便改的测试数据，实际上是不同用户名的，不要在意）
[/quote][/quote]

[二月十六]

那就用left join，然后判断有没有课程

SELECT *
  FROM (   SELECT T.UserName,
                  T.StudentID,
                  SUM(CASE WHEN C.StudentID IS NOT NULL THEN 1 ELSE 0 END) AS 门数,
                  ROW_NUMBER() OVER (ORDER BY SUM(CASE WHEN C.StudentID IS NOT NULL THEN 1 ELSE 0 END) DESC) [row_number]
             FROM Student T
             LEFT JOIN Course C
               ON C.StudentID = T.StudentID
            GROUP BY T.UserName,
                     T.StudentID) a
 WHERE row_number BETWEEN 1 AND 10;

[stelf]

第一个查询，你的门数从哪里来？你放在一个语句里面字段都没查出来怎么引用

[学海无涯-回头是岸]

你 Student没有门数 所以 ORDER BY 门数 desc 报错

[scyyzgxh]

引用 1 楼 stelf 的回复:

第一个查询，你的门数从哪里来？你放在一个语句里面字段都没查出来怎么引用

引用 2 楼 学海无涯-回头是岸 的回复:

你 Student没有门数
所以 ORDER BY 门数 desc 报错

谢谢你们的回复，这个我根据提示已经知道问题在哪，所以我现在是求一种解决方法，而不是问法一为什么不行，当然法一那种写法如果能行，我是可以接受的，法二两次一样的查询感觉不舒服

[二月十六]

试试这样，没测试

SELECT *
  FROM (   SELECT T.UserName,
                  T.StudentID,
                  COUNT(1) AS 门数,
                  ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) [row_number]
             FROM Student T
             JOIN Course C
               ON C.StudentID = T.StudentID
            GROUP BY T.UserName,
                     T.StudentID) a
 WHERE row_number BETWEEN 1 AND 10;

[stelf]

例子如下： SELECT * FROM (select t.username, t.studentid, count(1), '门数', ROW_NUMBER() OVER(ORDER BY count(1) desc) as row_number from student t inner join Course C on C.StudentID = T.StudentID group by t.username, t.studentid) mt WHERE row_number between 1 and 10;

[scyyzgxh]

引用 4 楼 二月十六 的回复:

试试这样，没测试

SELECT *
  FROM (   SELECT T.UserName,
                  T.StudentID,
                  COUNT(1) AS 门数,
                  ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) [row_number]
             FROM Student T
             JOIN Course C
               ON C.StudentID = T.StudentID
            GROUP BY T.UserName,
                     T.StudentID) a
 WHERE row_number BETWEEN 1 AND 10;

能运行，但似乎结果不太正确呢，它只有一行数据，应该是显示有选课的学生，但是没有选课的学生不显示了
将里面单独抽出来改为left join，可以显示其它学生的情况，但是门数不正确，没有选课的学生门数全部显示为1了
（里面有用户名重复的是我随便改的测试数据，实际上是不同用户名的，不要在意）