56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> delimiter //
mysql> CREATE PROCEDURE dnt_getuserlist()
-> begin
-> declare pagesize int;
-> declare pageindex int;
-> declare orderby varchar(200);
-> DECLARE pagetop int;
-> DECLARE `sql` varchar(1000);
-> SET pagetop = (pageindex-1)*pagesize;
-> IF pageindex = 1 then
-> SET `sql`= concat( 'SELECT TOP ' , pagesize , ' [dnt_users].[uid] FROM [dnt_users] LEFT JOIN [dnt_userfields] ON [dnt_userfields].[uid] = [dnt_users].[uid] ' , orderby);
-> ELSE
-> SET `sql` = concat('SELECT TOP ' , pagesize , ' [dnt_users].[uid], [dnt_users].[username], [dnt_users].[nickname], [dnt_users].[joindate], [dnt_users].[credits], [dnt_users].[posts], [dnt_users].[email], [dnt_users].[lastactivity], [dnt_userfields].[location] FROM [dnt_users] LEFT JOIN [dnt_userfields]ON [dnt_userfields].[uid] = [dnt_users].[uid] WHERE [dnt_users].[uid] NOT IN (SELECT TOP ' , pagesize , ' [uid] FROM [dnt_users] ' , orderby , ') ', replace(orderby,'WHERE','AND'));
-> END IF;
-> select `sql`;
-> set @sSQL=`sql`;
-> PREPARE stmt1 FROM @sSQL;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
->
-> end;//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>