set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/*
* create by huang qing
* modify by chaoxueling
* modify date 2013-05-15
*/
ALTER PROCEDURE [dbo].[sp_GetPropsTop_ByPropsCount]
@PageIndex int,
@PageSize int,
@OrderType int
AS
BEGIN
if(@OrderType=1)
begin
with Mem_TbUserPropsTop as
(
select A.*,b.PropsCountB,ROW_NUMBER() over (order by PropsCountA desc) as rowNumber from (
select QdUserId,PropsId,propscnt as PropsCountA from tbuserpropscnt where propsid=153
) as A
full join(
select QdUserId,PropsId,propscnt as PropsCountB from tbuserpropscnt where propsid=154
) as B
on a.qduserid=b.qduserid
)
select qduserid,PropsId,ISNULL(PropsCountA,0) as PropsCountA,ISNULL(PropsCountB,0) as PropsCountB, rowNumber from Mem_TbUserPropsTop
where rowNumber>@PageSize*(@PageIndex-1) and RowNumber<=@PageSize*@PageIndex
order by PropsCountA desc
end
else if(@OrderType=0)
begin
with Mem_TbUserPropsTop as
(
select A.*,b.PropsCountA,ROW_NUMBER() over (order by PropsCountB desc) as rowNumber from (
select QdUserId,PropsId,propscnt as PropsCountB from tbuserpropscnt where propsid=154
) as A
full join(
select QdUserId,PropsId,propscnt as PropsCountA from tbuserpropscnt where propsid=153
) as B
on a.qduserid=b.qduserid
)
select qduserid,PropsId,ISNULL(PropsCountA,0) as PropsCountA,ISNULL(PropsCountB,0) as PropsCountB, rowNumber from Mem_TbUserPropsTop
where rowNumber>@PageSize*(@PageIndex-1) and RowNumber<=@PageSize*@PageIndex
order by PropsCountB desc
end
else
begin
with Mem_TbUserPropsTop as
(
select A.*,b.PropsCountA,ROW_NUMBER() over (order by PropsCountB desc) as rowNumber from (
select QdUserId,PropsId,propscnt as PropsCountB from tbuserpropscnt where propsid=154
) as A
full join(
select QdUserId,PropsId,propscnt as PropsCountA from tbuserpropscnt where propsid=153
) as B
on a.qduserid=b.qduserid
)
select qduserid,PropsId,ISNULL(PropsCountA,0) as PropsCountA,ISNULL(PropsCountB,0) as PropsCountB, rowNumber from Mem_TbUserPropsTop
where rowNumber>@PageSize*(@PageIndex-1) and RowNumber<=@PageSize*@PageIndex
order by PropsCountB desc
end
END
和
order by case when @OrderType=1 then PropsCountA when @OrderType=0 then PropsCountB when @OrderType=2 then PropsCountC else PropsCountD end desc