Masters! Ask a pagination plus filter plus sort sql!

diablo18

Now there is such a SQL:
select top 50 * from
(select * from (select * from v_cmm_club_prize_joined_users where prizecode = 3) g

where ID not in (select top 0 id from
(select * from v_cmm_club_prize_joined_users where prizecode = 3) g)) a


For pagination and query, where v_cmm_club_prize_joined_users is written
SELECT TOP 100 PERCENT b.ORG_NAME AS orgname, a.id, a.psid, a.prizecode, a.joindate, a.username, a.tel, a.gatewayid, a.telprize, a.pwd, a.orgid
FROM dbo.CMM_CLUB_PRIZE_JOINED_USERS AS a INNER JOIN
                      dbo.SYS_ORGANIZATION AS b ON a.orgid = b.ORG_ID
ORDER BY a.prizecode DESC, a.id





The program gets data for one page like this:
 rs = stmt.executeQuery ("select count (*) as amount from v_cmm_club_prize_joined_users" + where);
 if (rs.next ())
 dataSetSize = rs.getInt ("amount");
       String view = "(select * from v_cMM_club_prize_joined_users" + where + ") g";
String sql = "select top 50 * from (select * from" + view + "where ID not in (select top" + String.valueOf (startRow) + "id from" + view + ")) a";
Ranch
 Ranch
 rs = stmt.executeQuery (sql);
The idea is that after querying through where, you can first sort the result set after the query according to prizecode, id, and then divide the business.
But the data display in sqlserver seems to be out of order. What is going on?

prajna

select top 50 * from
(select * from (select * from v_cmm_club_prize_joined_users where prizecode = 3) g order by ...
Rank another order

prajna

Oh no

prajna

--Recommend this paging method for your reference, high efficiency, note: quote

select top page size *

from table1

where id>

      (select max (id) from

      (select top ((page-1) * page size) id from table1 order by id) as T

       )

  order by id

diablo18

But how do I sort the result set, I now sort by prizecode first, then sort by primary key
order by prizecode desc, id

恶魔右指

Boss Zou:
Efficiency is pretty good
/ *-Paging procedures implemented using stored procedures

 Display page X of the specified table, view, query result
 For the case of the primary key or identity column in the table, query directly from the original table, otherwise use the temporary table method
 This method is not recommended if there is a primary key in the view or query results

--Zou Jian 2003.09 (Please retain this information for reference)-* /

/ *-Call example
 exec p_show 'Regional Information'

 exec p_show 'Region data', 5, 3, 'Region number, region name, mnemonic code', 'region number'
-* /

if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_show]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1)
drop procedure [dbo]. [p_show]
GO

CREATE Proc p_show
@QueryStr nvarchar (4000), --table name, view name, query statement
@PageSize int = 10, --the size of each page (number of rows)
@PageCurrent int = 1, --Page to display
@FdShow nvarchar (4000) = '', --A list of fields to be displayed. If the query result has an identification field, this value needs to be specified and does not include an identification field
@FdOrder nvarchar (1000) = '' --sort field list
as
declare @FdName nvarchar (250)-primary key or table in the table, identity column name in the temporary table
 , @ Id1 varchar (20), @ Id2 varchar (20)-start and end record numbers
 @Obj_ID int-Object ID
--Handling of composite primary keys in the table
declare @strfd nvarchar (2000)-composite primary key list
 @strjoin nvarchar (4000)-join fields
 @strwhere nvarchar (2000)-query conditions


select @ Obj_ID = object_id (@QueryStr)
 , @ FdShow = case isnull (@FdShow, '') when '' then '*' else '' + @ FdShow end
 , @ FdOrder = case isnull (@FdOrder, '') when '' then '' else 'order by' + @ FdOrder end
 , @ QueryStr = case when @Obj_ID is not null then '' + @ QueryStr else '(' + @ QueryStr + ') a' end

--If the first page is displayed, it can be done directly with top
if @ PageCurrent = 1
begin
 select @ Id1 = cast (@PageSize as varchar (20))
 exec ('select top' + @ Id1 + @ FdShow + 'from' + @ QueryStr + @ FdOrder)
 return
end

-If it is a table, check if there is an identity or primary key in the table
if @Obj_ID is not null and objectproperty (@ Obj_ID, 'IsTable') = 1
begin
 select @ Id1 = cast (@PageSize as varchar (20))
  , @ Id2 = cast ((@ PageCurrent-1) * @ PageSize as varchar (20))

 select @ FdName = name from syscolumns where id = @ Obj_ID and status = 0x80
 if @@ rowcount = 0 --If there are no identity columns in the table, check if there is a primary key in the table
 begin
  if not exists (select 1 from sysobjects where parent_obj = @ Obj_ID and xtype = 'PK')
   goto lbusetemp-if there is no primary key in the table, use temporary table processing

  select @ FdName = name from syscolumns where id = @ Obj_ID and colid in (
   select colid from sysindexkeys where @ Obj_ID = id and indid in (
    select indid from sysindexes where @ Obj_ID = id and name in (
     select name from sysobjects where xtype = 'PK' and parent_obj = @ Obj_ID
   )))
  if @@ rowcount> 1-check if the primary key in the table is a composite primary key
  begin
   select @ strfd = ``, @ strjoin = '', @ strwhere = ''
   select @ strfd = @ strfd + ', [' + name + ']'
    , @ strjoin = @ strjoin + 'and a. [' + name + '] = b. [' + name + ']'
    , @ strwhere = @ strwhere + 'and b. [' + name + '] is null'
    from syscolumns where id = @ Obj_ID and colid in (
    select colid from sysindexkeys where @ Obj_ID = id and indid in (
     select indid from sysindexes where @ Obj_ID = id and name in (
      select name from sysobjects where xtype = 'PK' and parent_obj = @ Obj_ID
    )))
   select @ strfd = substring (@ strfd, 2,2000)
    , @ strjoin = substring (@ strjoin, 5,4000)
    , @ strwhere = substring (@ strwhere, 5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp

/ *-Processing method using identity column or primary key as a single field-* /
lbuseidentity:
 exec ('select top' + @ Id1 + @ FdShow + 'from' + @ QueryStr
  + 'where' + @ FdName + 'not in (select top'
  + @ Id2 + '' + @ FdName + 'from' + @ QueryStr + @ FdOrder
  + ')' + @ FdOrder
  )
 return

/ *-Handling method of composite primary key in table-* /
lbusepk:
 exec ('select' + @ FdShow + 'from (select top' + @ Id1 + 'a. * from
  (select top 100 percent * from '+ @ QueryStr + @ FdOrder +') a
  left join (select top '+ @ Id2 +' '+ @ strfd +'
  from '+ @ QueryStr + @ FdOrder +') b on '+ @ strjoin +'
  where '+ @ strwhere +') a '
  )
 return

/ *-Method of processing with temporary tables-* /
lbusetemp:
select @FdName = '[ID _' + cast (newid () as varchar (40)) + ']'
 , @ Id1 = cast (@PageSize * (@ PageCurrent-1) as varchar (20))
 , @ Id2 = cast (@ PageSize * @ PageCurrent-1 as varchar (20))

exec ('select' + @ FdName + '= identity (int, 0,1),' + @ FdShow + '
  into #tb from '+ @ QueryStr + @ FdOrder +'
 select '+ @ FdShow +' from #tb where '+ @ FdName +' between '
 + @ Id1 + 'and' + @ Id2
 )

GO