create table #temp ( barcode varchar(20), num int ) insert into #temp select 'a','1' union select 'b','3'declare @i int select @i=max(num) from #tempset rowcount @i select ID=identity(int, 1, 1) into #T from sysobjects, syscolumns set rowcount 0select a.* from #temp a, #T b where a.num>=b.ID--result barcode num -------------------- ----------- a 1 b 3 b 3 b 3(4 row(s) affected)
create table dbo.#temp ( barcode varchar(20), num int ) insert into #temp select 'a','1' union select 'b','3' union select 'c','4' union select 'd','3' union select 'f','5' SELECT b.* FROM (SELECT id,colid FROM syscolumns WHERE id=object_id('sysreferences')) a INNER JOIN #temp b ON a.colid<=b.num barcode num -------------------- ----------- a 1 b 3 b 3 b 3 c 4 c 4 c 4 c 4 d 3 d 3 d 3 f 5 f 5 f 5 f 5 f 5(16 row(s) affected)
如
b 4
结果是
b 4
b 4
b 4
b 4
(
barcode varchar(20),
num int
)
insert into #temp
select 'a','1'
union
select 'b','3'declare @i int
select @i=max(num) from #tempset rowcount @i
select ID=identity(int, 1, 1) into #T from sysobjects, syscolumns
set rowcount 0select a.* from #temp a, #T b where a.num>=b.ID--result
barcode num
-------------------- -----------
a 1
b 3
b 3
b 3(4 row(s) affected)
(
barcode varchar(20),
num int
)
insert into #temp
select 'a','1'
union
select 'b','3'
union
select 'c','4'
union
select 'd','3'
union
select 'f','5'
SELECT b.*
FROM
(SELECT id,colid
FROM syscolumns
WHERE id=object_id('sysreferences')) a
INNER JOIN #temp b
ON a.colid<=b.num
barcode num
-------------------- -----------
a 1
b 3
b 3
b 3
c 4
c 4
c 4
c 4
d 3
d 3
d 3
f 5
f 5
f 5
f 5
f 5(16 row(s) affected)