◎CREATEPROCBmhd_User_...... CREATE PROC Bmhd_User_GetConsumeByUserID @UserID INT, @PageIndex INT, @PageSize INT, @RecordCount INT AS DECLARE @PageLowerBound INT, @PageUpperBound INT, @RowsToReturn INT SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1 SET @RowsToReturn = @PageSize*(@PageIndex + 1) SET ROWCOUNT @RowsToReturn CREATE TABLE #PageIndex ( IndexID INT IDENTITY(1, 1) NOT NULL, OID INT ) INSERT INTO #PageIndex(OID) SELECT ID FROM UserConsumeLog WHERE UID = @UserID ORDER BY ID DESC CREATE TABLE #Result ( ID INT, UID INT, MediaType NVARCHAR(20), MediaName NVARCHAR(500), Money INT, DoneTime DATETIME ) INSERT INTO #Result SELECT u.ID, u.UID, CASE MediaType WHEN 0 THEN '音乐' WHEN 1 THEN '电视' WHEN 2 THEN '电影' WHEN 3 THEN '卡通电影' ELSE '未知' END, ISNULL (CASE MediaType WHEN 0 THEN (SELECT TOP 1 s.SongName FROM Song s WHERE s.SongID = u.MediaID) WHEN 1 THEN (SELECT TOP 1 t.TvName + '-' + ltrim(str(tu.TvNumber)) FROM TV t, TVUrl tu WHERE u.MediaID = tu.TvUrlID AND t.TvID = tu.TvID) WHEN 2 THEN (SELECT TOP 1 m.MovieName+'-'+ltrim(str(mu.MovieNumber)) FROM Movie m, MovieUrl mu WHERE u.MediaID = mu.MovieUrlID AND m.MovieID = mu.MovieID) WHEN 3 THEN (SELECT TOP 1 cm.CartoonMovieName+'-'+ltrim(str(cmu.CartoonMovieNumber)) FROM CartoonMovie cm, CartoonMovieUrl cmu WHERE u.MediaID = cmu.CartoonMovieUrlID AND cm.CartoonMovieID = cmu.CartoonMovieID) ELSE '未知' END,'未知'), u.Money, u.DoneTime FROM UserConsumeLog u, #PageIndex p WHERE u.ID = p.OID AND p.IndexID > @PageLowerBound AND p.IndexID < @PageUpperBound SELECT * FROM #Result ORDER BY ID DESC IF @RecordCount < 0 BEGIN SELECT COUNT(*) FROM UserConsumeLog WHERE uid = @userid END GO |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 23:40 , Processed in 0.117017 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.