if object_id('tempdb..#tmp') is not null
drop table #tmp
GO
----创建测试数据
declare @t table(DJBH VARCHAR(20),ID int,ShL numeric(20,2))
insert @t
select 'XSC001',24949,3 union all
select 'XSC001',21943,2
----生成临时表
select top 100 id = identity(int,0,1) into #tmp from syscolumns,sysobjects
----查询
select a.* from @t as a,#tmp as b
where b.id + 1 <= a.ShL
order by a.DJBH,a.ID DESCdrop table #tmp/*结果
DJBH ID ShL
------------------------------------------
XSC001 24949 3.00
XSC001 24949 3.00
XSC001 24949 3.00
XSC001 21943 2.00
XSC001 21943 2.00
*/
drop table #tmp
GO
----创建测试数据
declare @t table(DJBH VARCHAR(20),ID int,ShL numeric(20,2))
insert @t
select 'XSC001',24949,3 union all
select 'XSC001',21943,2
----生成临时表
select top 100 id = identity(int,0,1) into #tmp from syscolumns,sysobjects
----查询
select a.* from @t as a,#tmp as b
where b.id + 1 <= a.ShL
order by a.DJBH,a.ID DESCdrop table #tmp/*结果
DJBH ID ShL
------------------------------------------
XSC001 24949 3.00
XSC001 24949 3.00
XSC001 24949 3.00
XSC001 21943 2.00
XSC001 21943 2.00
*/
用一条语句能不能搞定?不要用存储过程。因为我这个要返回很多值的,这样写太麻烦了。
顶
1.应用程序第一次运行时生成ID全局临时表
select top 100 id = identity(int,0,1) into ##tmp from syscolumns,sysobjects
2.以后每次循环时只调用下面的语句就可以了,不必再重新生成ID临时表
select a.* from @t as a,##tmp as b
where b.id + 1 <= a.ShL
order by a.DJBH,a.ID DESC
象上面的数量是5,我这里放到实例中去后每个ID都有5行
例如:将表 customers 重命名为 custs
EXEC sp_rename 'customers', 'custs'