Sort paging problem

christina

Suppose there are three fields in a table: company name, information name, ID

Company name can be duplicated

Now to paginate this table (10 items per page)

How to prevent the company name from appearing repeatedly on each page, regardless of whether the information name is repeated

恶魔右指

Use this, high efficiency
The first is based on (unique identifier and primary key, composite primary key), if not through a temporary table
/*--Paging program implemented with stored procedures

Page X showing the specified table, view, query results
For the case of the primary key or identity column in the table, directly fetch the query from the original table, otherwise use the temporary table method
If there is a primary key in the view or query results, this method is not recommended

--Zou Jian 2003.09 (please keep this information for reference)--*/

/*--Call example
exec p_show'Regional information'

exec p_show'Area Information', 5, 3,'Area Number, Area Name, Mnemonic Code','Area 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 lines)
@PageCurrent int=1, --page to be displayed
@FdShow nvarchar (4000)=``, ---list of fields to be displayed, if the query result has an identification field, you need to specify this value, and do not include the identification field
@FdOrder nvarchar (1000)='' --sort field list
as
declare @FdName nvarchar(250)-the primary key in the table or the identity column name in the table, temporary table
,@Id1 varchar(20),@Id2 varchar(20)-the start and end record number
, @Obj_ID int - object ID
- Processing of composite primary keys in the table
declare @strfd nvarchar(2000) - list of composite primary keys
, @strjoin nvarchar(4000) - connect fields
,@strwhere nvarchar(2000) --Query condition


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, you can directly use top to complete
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 identifier 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 is no identity column 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 the temporary table

  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

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

/*--The processing method of composite primary key in the 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 table--*/
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

kxiaper

For your reference:
create PROCEDURE dbo.GetPagingRecord
    (
        @tablename varchar(100), --table name or view table
        @fieldlist varchar(4000)='*',--To select the field list
        @orderfield varchar(100),--sort field
        @keyfield varchar(100),-primary key
        @pageindex int, --page number, starting from 0
        @pagesize int=20,--page size
        @strwhere varchar(4000),--condition
        @ordertype bit=1--sort, 1, descending, 0, ascending
    )
AS
/**//*
Name: GetPagingRecord
Role: sort and page by any field
*/
    SET NOCOUNT ON
    declare @sqlstr varchar(6000)
    - Deal with dangerous characters in SQL, and process the condition into a form that is easy to embed
    set @strwhere=replace(@strwhere,'''','''''')
    set @strwhere=replace(@strwhere,'--','')
    set @strwhere=replace(@strwhere,';','')
    set @sqlstr='declare @CurPageNum int;'
    set @sqlstr=@sqlstr+'declare @nextpagenum int;'
    set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
    if @ordertype=1
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=``select'+@fieldlist+' from (select top''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where'+@strwhere+' order by'+@orderfield+' desc) as a where'+@keyfield+' not in (

select top''+cast(@curpagenum as varchar)+'''+@keyfield+' from'+@tablename+' where'+@strwhere+'

order by'+@orderfield+' desc) order by'+@orderfield+' desc'';'
    end
    else
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=``select'+@fieldlist+' from (select top''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where'+@strwhere+' order by'+@orderfield+' asc) as a where'+@keyfield+' not in (

select top''+cast(@curpagenum as varchar)+'''+@keyfield+' from'+@tablename+' where'+@strwhere+'

order by'+@orderfield+' asc) order by'+@orderfield+' asc'';'
    end
    set @sqlstr=@sqlstr+'execute( @sqlstr)'
    --print @sqlstr
    execute(@sqlstr)

wgfxman

Company name can be repeated

Now we need to page this table (10 entries per page)

How to keep the company name from repeating on each page, regardless of whether the information name is repeated

----------------------------------
This is not just paging

恶魔右指

The landlord uses the above to generate stored procedures
Just check

christina

Thank you for the answer from upstairs. It seems that the upstairs can't achieve the effect I want. I request that the company name cannot be repeated on one page. The stored procedures of the upstairs are likely to have the same company.

to:wgfxman
Is there any good way, brother? It's good to talk about ideas. Thank you
Everyone, go ahead.

christina

If the three fields are not enough, you can add additional fields.

wgfxman

Is a bit complicated...
1. Realize paging
2. A field on the same page cannot be repeated
3. Records with duplicate key fields cannot be omitted

Talk about my general idea:

1. A temporary table is needed, and the structure is more than the original table. PAGE (can be created by SELECT INTO) ORDER BY Company name
2. Define @CUR_KEY, CUR_PAGE, CURROW_OF_PAGE
   SELECT @CUR_KEY=''
   SELECT @CUR_PAGE=1
   SELECT @CURROW_OF_PAGE=1

3. Find out 10 records that are not duplicated by company name and have not been paged (PAGE=default value 0), SET PAGE=@CUR_PAGE
    (Can be judged by @CUR_PAGE<>company name,
        If established:
            SET PAGE=@CUR_PAGE,
            SET @CUR_PAGE=Company name
            SET @CURROW_OF_PAGE=@CURROW_OF_PAGE+1
    )

4. SET @CUR_PAGE=@CUR_PAGE+1

5. Repeat 3 and 4

If you are worried that the last few records may have the problem of keyword duplication, you need to handle it specially:

1. Establish a comparison table of keywords, COUNT (keyword), that is, the number of keywords, you can only keep COUNT (keyword)>1
2. In the above execution process, the temporary table should be associated with the comparison table of the number of keywords (LEFT JOIN can be used), ORDER BY number of keywords + keywords
3. The other steps remain unchanged
4. If there is a situation where the number of the same keyword> the total number of pages, there is no way. . .

This is just an occasional algorithm to solve this problem. The efficiency is definitely not as good as those of the prawn algorithm above, just to solve the problem~

christina

Thank you upstairs.
top. Continue to find ideas