select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b select top 4 a.rowid b.Title,b.Pic_Name,b.Url from #t a, table b where a.rowid *= b.adv_position select * from #tdrop table #t
select id=identity(int,1,1),Title,Pic_Name,Url,adv_position from Advertising WHERE Show = 1 order by adv_position
如果你能保证你的记录都生成 1,2,3,4这样的序号,我倒有个办法create table t_seq (seqid int) go insert into t_seq values(1) insert into t_seq values(2) insert into t_seq values(3) insert into t_seq values(4) goselect a.*,b.seqid from ( select top 4 Title,Pic_Name,Url,adv_position from Advertising WHERE Show = 1 order by adv_position) a right join t_seq b on a.adv_position = b.seqid大致就是这样,我没有测试,可能语法有点问题。------------------------------------ http://chinadba.cn 深圳骄子数据库服务网 最具实战经验的数据库优化、培训、设计、管理网站。 下一步更新优化文档栏目
select id=identity(int,1,1),Title,Pic_Name,Url,adv_position into #t from Advertising WHERE Show = 1 order by adv_position
declare @t table(title char(10),pic_name char(30),url char(10),adv_position int) insert into @t select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all select '位置4','2005-12-2913-56-47-banner2.jpg','#',4 select * from @t select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b select top 4 a.rowid,b.Title,b.Pic_Name,b.Url from #t a, @t b where a.rowid *= b.adv_position select * from #tdrop table #t /*rowid Title Pic_Name Url ----------- ---------- ------------------------------ ---------- 1 NULL NULL NULL 2 NULL NULL NULL 3 位置3 2005-12-2913-57-15-banner1.jpg # 4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行) */
declare @t table(title char(10),pic_name char(30),url char(10),adv_position int) insert into @t select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all select '位置4','2005-12-2913-56-47-banner2.jpg','#',4 select * from @t select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b select top 4 a.rowid,isnull(b.Title,''),isnull(b.Pic_Name,''),isnull(b.Url,'') from #t a, @t b where a.rowid *= b.adv_position select * from #tdrop table #t /* rowid ----------- ---------- ------------------------------ ---------- 1 2 3 位置3 2005-12-2913-57-15-banner1.jpg # 4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行) */
declare @t table(title char(10),pic_name char(30),url char(10),adv_position int) insert into @t select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all select '位置4','2005-12-2913-56-47-banner2.jpg','#',4 select * from @t select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b select top 4 a.rowid,isnull(b.Title,'') as title ,isnull(b.Pic_Name,'') as pic_name,isnull(b.Url,'') as url from #t a, @t b where a.rowid *= b.adv_position select * from #tdrop table #t /* rowid title pic_name url ----------- ---------- ------------------------------ ---------- 1 2 3 位置3 2005-12-2913-57-15-banner1.jpg # 4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行) */
select * from ( select top 4 Title,Pic_Name,Url,adv_position from (select Title,Pic_Name,Url,adv_position from Advertising union all select '','','',1 union all select '','','',2 union all select '','','',3 union all select '','','',4 ) a WHERE Show = 1 ) b order by adv_position
select top 4 a.rowid b.Title,b.Pic_Name,b.Url from #t a, table b
where a.rowid *= b.adv_position
select * from #tdrop table #t
(seqid int)
go
insert into t_seq values(1)
insert into t_seq values(2)
insert into t_seq values(3)
insert into t_seq values(4)
goselect a.*,b.seqid from (
select top 4 Title,Pic_Name,Url,adv_position from Advertising WHERE Show = 1 order by adv_position) a right join t_seq b on a.adv_position = b.seqid大致就是这样,我没有测试,可能语法有点问题。------------------------------------
http://chinadba.cn
深圳骄子数据库服务网
最具实战经验的数据库优化、培训、设计、管理网站。
下一步更新优化文档栏目
into #t
from Advertising
WHERE Show = 1
order by adv_position
insert into @t
select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all
select '位置4','2005-12-2913-56-47-banner2.jpg','#',4
select * from @t
select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b
select top 4 a.rowid,b.Title,b.Pic_Name,b.Url from #t a, @t b
where a.rowid *= b.adv_position
select * from #tdrop table #t
/*rowid Title Pic_Name Url
----------- ---------- ------------------------------ ----------
1 NULL NULL NULL
2 NULL NULL NULL
3 位置3 2005-12-2913-57-15-banner1.jpg #
4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行)
*/
insert into @t
select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all
select '位置4','2005-12-2913-56-47-banner2.jpg','#',4
select * from @t
select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b
select top 4 a.rowid,isnull(b.Title,''),isnull(b.Pic_Name,''),isnull(b.Url,'') from #t a, @t b
where a.rowid *= b.adv_position
select * from #tdrop table #t
/*
rowid
----------- ---------- ------------------------------ ----------
1
2
3 位置3 2005-12-2913-57-15-banner1.jpg #
4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行)
*/
insert into @t
select '位置3','2005-12-2913-57-15-banner1.jpg', '#',3 union all
select '位置4','2005-12-2913-56-47-banner2.jpg','#',4
select * from @t
select top 2000 rowid = identity(int,1,1) into #t from sysobjects a, sysobjects b
select top 4 a.rowid,isnull(b.Title,'') as title ,isnull(b.Pic_Name,'') as pic_name,isnull(b.Url,'') as url from #t a, @t b
where a.rowid *= b.adv_position
select * from #tdrop table #t
/*
rowid title pic_name url
----------- ---------- ------------------------------ ----------
1
2
3 位置3 2005-12-2913-57-15-banner1.jpg #
4 位置4 2005-12-2913-56-47-banner2.jpg # (所影响的行数为 4 行)
*/
from
( select top 4
Title,Pic_Name,Url,adv_position
from
(select Title,Pic_Name,Url,adv_position
from Advertising
union all
select '','','',1
union all
select '','','',2
union all
select '','','',3
union all
select '','','',4 ) a
WHERE
Show = 1 ) b
order by
adv_position