1. 没有重复值的
2. 有重复值的,就是有这样的表,一个商品属于多个分类的,联合起来的视图中这条商品就有多条记录,但是显示只要显示一条.
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(1-1)*12
set @PageUpperBound=@PageLowerBound+12
create table #pageindex2(id int identity(1,1) not null,nid varchar(120) )
INSERT INTO #pageindex2(nid)
select V_ForSort.gdsmst_gdsid
FROM V_ForSort
where 1=1 and ( rackcode like 'E%' or gdsmst_rackcode like 'E%')
order by gdsmst_beginstart desc
create table #pageindex3(id int not null,nid varchar(120) )
INSERT INTO #pageindex3(nid,id) select nid,id FROM #pageindex2
where nid not in( select nid from #pageindex2 group by nid having count(nid)>1)
declare @nid varchar(120)
declare @id int
declare hc cursor local fast_forward for
select nid from #pageindex2 group by nid having count(nid)>1
open hc
fetch hc into @nid
while(@@fetch_status <> -1)
begin
if(EXISTS(select top 1 id from #pageindex2 where nid=@nid order by id))
begin
select top 1 @id=id from #pageindex2 where nid=@nid order by id
INSERT INTO #pageindex3(nid,id) values(@nid,@id)
end
fetch next from hc into @nid
end
close hc
deallocate hc
create table #pageindex(id int identity(1,1) not null,nid varchar(120) )
INSERT INTO #pageindex(nid)
select nid FROM #pageindex3
order by id
select distinct p.id, gdsmst_gdsid,
gdsmst_gdsname, gdsmst_saleprice,
gdsmst_webprice, gdsmst_memberprice,
gdsmst_vipprice, platina_price,
gdsmst_imgurl, gdsmst_validflag
from V_ForSort,#pageindex as p
where (V_ForSort.gdsmst_gdsid = p.nid)
and (p.id>@PageLowerBound)
and (p.id<=@PageUpperBound)
order by p.id
drop table #pageindex
drop table #pageindex2
drop table #pageindex3