关于 rownum()over()的sql难题

Question

WITH PagedResult
AS 
(
SELECT DISTINCT ROW_NUMBER() over 
(ORDER BY 
 Case When @SortExpression='Name ASC' Then [Name] End,
 Case When @SortExpression='Name DESC' Then [Name] End Desc,
 Case when @sortExpression='Description ASC' Then [Description] End,
 Case when @sortExpression='Description DESC' Then [Description] End Desc,

 
 p.PatentSetId) as RowNum,
p.PatentSetId,
p.Name,
p.Description,

  (SELECT  Count(pp.PatentId) FROM PatentSet_Patent pp
 INNER JOIN  Patent ON  pp.PatentId = Patent.PatentId 
                     INNER JOIN PatentPublication ON  pp.PatentId = PatentPublication.PatentId and PatentPublication.DisplayPublication=1
 WHERE pp.PatentSetId = p.PatentSetId) as NumberOfPatents,
TotalRows=Count(*) OVER()

FROM PatentSet p

WHERE 
(@Name Is Null OR p.Name Like @Name + '%')
AND p.OrganizationId = @OrganizationId
AND p.CreatedBy = @CreatedBy

)
如果我想排序NumberOfPatents怎么办

Answer 1

WITH PagedResult
AS  
(
SELECT DISTINCT ROW_NUMBER() over  
(
    ORDER BY  
Case When @SortExpression='Name ASC' Then [Name] End,
Case When @SortExpression='Name DESC' Then [Name] End Desc,
Case when @sortExpression='Description ASC' Then [Description] End,
Case when @sortExpression='Description DESC' Then [Description] End Desc, 
p.PatentSetId,
NumberOfPatents 
) as RowNum,
p.PatentSetId,
p.Name,
p.Description,
TotalRows=Count(*) OVER()
FROM 
(
select *,
(SELECT Count(pp.PatentId) FROM PatentSet_Patent pp
INNER JOIN Patent ON pp.PatentId = Patent.PatentId  
  INNER JOIN PatentPublication ON pp.PatentId = PatentPublication.PatentId and PatentPublication.DisplayPublication=1
WHERE pp.PatentSetId = p.PatentSetId) as NumberOfPatents
from PatentSet 
) p
WHERE  
    (@Name Is Null OR p.Name Like @Name + '%')
     AND p.OrganizationId = @OrganizationId
     AND p.CreatedBy = @CreatedBy
)

Answer 2

就将子查询放在CASE 后面即可，不知有没问题，我测试是可以这样 的。只是语法看起来较麻烦而已。不行的话就再写一个CTE了
WITH PagedResult
AS  
(SELECT 
DISTINCT ROW_NUMBER() over  (ORDER BY  Case When @SortExpression='Name ASC' Then [Name] End,
Case When @SortExpression='Name DESC' Then [Name] End Desc,
Case when @sortExpression='Description ASC' Then [Description] End,
Case when @sortExpression='Description DESC' Then [Description] End Desc,p.PatentSetId,
(
SELECT Count(pp.PatentId) FROM PatentSet_Patent pp
INNER JOIN Patent ON pp.PatentId = Patent.PatentId  
INNER JOIN PatentPublication ON pp.PatentId = PatentPublication.PatentId and PatentPublication.DisplayPublication=1
WHERE pp.PatentSetId = p.PatentSetId)) as RowNum,
p.PatentSetId,
p.Name,
p.Description,(
SELECT Count(pp.PatentId) FROM PatentSet_Patent pp
INNER JOIN Patent ON pp.PatentId = Patent.PatentId  
INNER JOIN PatentPublication ON pp.PatentId = PatentPublication.PatentId and PatentPublication.DisplayPublication=1
WHERE pp.PatentSetId = p.PatentSetId) as NumberOfPatents,TotalRows=Count(*) OVER()　FROM PatentSet p　WHERE  (@Name Is Null OR p.Name Like @Name + '%')　AND p.OrganizationId = @OrganizationId
AND p.CreatedBy = @CreatedBy
) 

Answer 3

需要做临时表，这样做不行
with ..
as
()
,..
as
()

Answer 4

我觉得没问题啊。请查看以下语句 
 
create table #t1  ( id int, name varchar(10)) --用户
create table #t2  ( id int, addr varchar(10)) --工作地点(一个人有多个地点）insert into #t1 select 1,'zuan' union all select 2,'zhang' union all select 3,'wang'insert into #t2 select 1,'beijing' union all select 1,'shanghai' union all select 2,'guangzhou' union all select 3,'dongjing'
 
--1、子查询放在order by 后面
;
with cte as 
(
select row_number() over (order by name,(select count(1) from #t2 b where b.id=a.id) ) num,id,name  from #t1 a )
select *from cte--2、
;
with cte as 
(
select row_number() over (order by name) num,id,name,(select count(1) from #t2 b where b.id=a.id) addcount from #t1 a),
cte2 as
(select row_number() over (order by name,addcount) num2,id,name from cte)
select *from cte2--3、
;
with cte as 
(
select row_number() over (order by name) num,id,name,(select count(1) from #t2 b where b.id=a.id) addcount from #t1 a) 
select row_number() over (order by name,addcount) num2,id,name from cte 
num2                 id          name
-------------------- ----------- ----------
1                    3           wang
2                    2           zhang
3                    1           zuan(3 行受影响)