SQL 动态排序的部分情况实现

原创: willonboy@sina.com set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <william zhang> -- Create date: <2010-10-29> -- Description: <GET HD app product list and order by @orderBy desc -- return uidID -- ,szTitle -- ,szBrief -- ,szDescription -- ,szTypeID -- ,szTermCondition -- ,ntPayingway -- ,ntState -- ,dtDate -- ,szTypeName -- ,szThumbnailImage -- ,ntRepute -- ,ntDisrepute -- ,ntScore -- ,ntDownloadCount field> -- ============================================= -- Version: -- new 1.0.0 (2010-12-07| william ) -- ============================================= -- Deploy Version: -- develop server: 1.0.0 -- staging server: null -- live server: null -- ============================================= -- Modify: (format:| date | user | description |) -- ============================================= ALTER PROCEDURE UP_GetHDProductList @orderBy nvarchar(50)='dtDate',--可选项==> 时间(默认):'dtDate'; 下载次数:'ntDownloadCount'; 评分:'ntScore'; 好评次数:'ntRepute' @IsHD BIT =0 , @IsUserUpload BIT = 0, @pageIndex INT=1, @pageSize INT =10, @pageCount INT OUT, @recordCount INT OUT AS BEGIN --START PROCEDURE SET NOCOUNT ON DECLARE @Count INT SELECT @Count = (@pageSize * @pageIndex) SELECT @recordCount = COUNT(tb_Products.uidID) FROM tb_products WHERE 1=1 AND tb_Products.bHD = @IsHD AND tb_Products.ntIsUserUpload = @IsUserUpload AND tb_Products.szTypeID NOT LIKE '301%' SELECT @pageCount = CEILING( @recordCount * 1.0 / @pageSize ) --下面是执行分页 IF @pageindex = 1 BEGIN SELECT TOP(@pageSize) tb_Products.uidID ,tb_Products.szTitle ,tb_Products.szBrief ,tb_Products.szDescription ,tb_Products.szTypeID ,tb_Products.szTermCondition ,tb_Products.ntPayingway ,tb_Products.ntState ,tb_Products.dtDate ,tb_ProductType.szName AS szTypeName ,dbo.fn_GetThumbnailImage(tb_Products.uidID) AS szThumbnailImage ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0) AS ntRepute ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,-1) AS ntDisrepute ,dbo.fn_GetProductScore(tb_Products.uidID) AS ntScore ,dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0) AS ntDownloadCount FROM tb_Products LEFT JOIN tb_ProductType ON tb_Products.szTypeID = tb_ProductType.szID WHERE tb_products.ntState = 2 AND tb_Products.bHD = @IsHD AND tb_Products.ntIsUserUpload = @IsUserUpload AND tb_Products.szTypeID NOT LIKE '301%' ORDER BY CASE @orderBy WHEN 'dtDate' THEN dtDate WHEN 'ntRepute' THEN cast(ltrim(dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0)) as int) WHEN 'ntScore' THEN cast(ltrim(dbo.fn_GetProductScore(tb_Products.uidID)) as int) WHEN 'ntDownloadCount' THEN cast(ltrim(dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0)) as int) ELSE NULL END DESC END ELSE IF @pageIndex > 1 AND @pageIndex < @pageCount --willonboy@sina.com BEGIN SELECT uidID ,szTitle ,szBrief ,szDescription ,szTypeID ,szTermCondition ,ntPayingway ,ntState ,dtDate ,szTypeName ,szThumbnailImage ,ntRepute ,ntDisrepute ,ntScore ,ntDownloadCount FROM ( SELECT TOP(@pageSize) * FROM ( SELECT TOP(@Count) tb_Products.uidID ,tb_Products.szTitle ,tb_Products.szBrief ,tb_Products.szDescription ,tb_Products.szTypeID ,tb_Products.szTermCondition ,tb_Products.ntPayingway ,tb_Products.ntState ,tb_Products.dtDate ,tb_ProductType.szName AS szTypeName ,dbo.fn_GetThumbnailImage(tb_Products.uidID) AS szThumbnailImage ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0) AS ntRepute ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,-1) AS ntDisrepute ,dbo.fn_GetProductScore(tb_Products.uidID) AS ntScore ,dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0) AS ntDownloadCount FROM tb_Products LEFT JOIN tb_ProductType ON tb_Products.szTypeID = tb_ProductType.szID WHERE tb_products.ntState = 2 AND tb_Products.bHD = @IsHD AND tb_Products.ntIsUserUpload = @IsUserUpload AND tb_Products.szTypeID NOT LIKE '301%' ORDER BY CASE @orderBy WHEN 'dtDate' THEN dtDate WHEN 'ntRepute' THEN cast(ltrim(dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0)) as int) WHEN 'ntScore' THEN cast(ltrim(dbo.fn_GetProductScore(tb_Products.uidID)) as int) WHEN 'ntDownloadCount' THEN cast(ltrim(dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0)) as int) ELSE NULL END DESC ) AS T ORDER BY CASE @orderBy WHEN 'dtDate' THEN CAST(T.dtDate AS varchar(50)) WHEN 'ntRepute' THEN CAST(T.ntRepute AS varchar(50)) WHEN 'ntScore' THEN CAST(T.ntScore AS varchar(50)) WHEN 'ntDownloadCount' THEN CAST(T.ntDownloadCount AS varchar(50)) ELSE NULL END ASC) TT ORDER BY CASE @orderBy WHEN 'dtDate' THEN CAST(TT.dtDate AS varchar(50)) WHEN 'ntRepute' THEN CAST(TT.ntRepute AS varchar(50)) WHEN 'ntScore' THEN CAST(TT.ntScore AS varchar(50)) WHEN 'ntDownloadCount' THEN CAST(TT.ntDownloadCount AS varchar(50)) ELSE NULL END DESC END ELSE IF @pageIndex = @pageCount BEGIN SELECT uidID ,szTitle ,szBrief ,szDescription ,szTypeID ,szTermCondition ,ntPayingway ,ntState ,dtDate ,szTypeName ,szThumbnailImage ,ntRepute ,ntDisrepute ,ntScore ,ntDownloadCount FROM ( SELECT TOP(@RecordCount-(@pageCount-1)*@pageSize) tb_Products.uidID ,tb_Products.szTitle ,tb_Products.szBrief ,tb_Products.szDescription ,tb_Products.szTypeID ,tb_Products.szTermCondition ,tb_Products.ntPayingway ,tb_Products.ntState ,tb_Products.dtDate ,tb_ProductType.szName AS szTypeName ,dbo.fn_GetThumbnailImage(tb_Products.uidID) AS szThumbnailImage ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0) AS ntRepute ,dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,-1) AS ntDisrepute ,dbo.fn_GetProductScore(tb_Products.uidID) AS ntScore ,dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0) AS ntDownloadCount FROM tb_Products LEFT JOIN tb_ProductType ON tb_Products.szTypeID = tb_ProductType.szID WHERE tb_products.ntState = 2 AND tb_Products.bHD = @IsHD AND tb_Products.ntIsUserUpload = @IsUserUpload AND tb_Products.szTypeID NOT LIKE '301%' ORDER BY CASE @orderBy WHEN 'dtDate' THEN dtDate WHEN 'ntRepute' THEN cast(ltrim(dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0)) as int) WHEN 'ntScore' THEN cast(ltrim(dbo.fn_GetProductScore(tb_Products.uidID)) as int) WHEN 'ntDownloadCount' THEN cast(ltrim(dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0)) as int) ELSE NULL END ASC ) AS T ORDER BY CASE @orderBy WHEN 'dtDate' THEN CAST(T.dtDate AS varchar(50)) WHEN 'ntRepute' THEN CAST(T.ntRepute AS varchar(50)) WHEN 'ntScore' THEN CAST(T.ntScore AS varchar(50)) WHEN 'ntDownloadCount' THEN CAST(T.ntDownloadCount AS varchar(50)) ELSE NULL END DESC END END --END PROCEDURE GO --Test Code /** DECLARE @pageCount int ,@recordCount int EXEC UP_GetHDProductList 'ntDownloadCount',0,0, 1, 1500, @pageCount out, @recordCount out select @pageCount , @recordCount **/ --Test Code 注意: NO.1 CASE @orderBy WHEN 'dtDate' THEN CAST(T.dtDate AS varchar(50)) WHEN 'ntRepute' THEN CAST(T.ntRepute AS varchar(50)) WHEN 'ntScore' THEN CAST(T.ntScore AS varchar(50)) WHEN 'ntDownloadCount' THEN CAST(T.ntDownloadCount AS varchar(50)) ELSE NULL 这一部分的when then 语句摆放不是随意的 必须安照 T-SQL 中定义的类型优先级倒序摆放 NO.2 CASE @orderBy WHEN 'dtDate' THEN dtDate WHEN 'ntRepute' THEN cast(ltrim(dbo.fn_GetReputeOrDisreputeByProductID(tb_Products.uidID,0)) as int) WHEN 'ntScore' THEN cast(ltrim(dbo.fn_GetProductScore(tb_Products.uidID)) as int) WHEN 'ntDownloadCount' THEN cast(ltrim(dbo.fn_GetProdutDownloadCountByProductIDandDatePeriod(tb_Products.uidID,0)) as int) ELSE NULL 对于这部分函数列 只能用函数调用方式(因为不支持假名!!)