可以吧。 DECLARE @t TABLE(a INT,b INT,c int) INSERT INTO @t SELECT 1001,1,3;SELECT a,b.number AS b,b.number AS c FROM @t a JOIN master..spt_values b ON b.number BETWEEN a.b AND a.c AND b.type='p'/* a b c ----------- ----------- ----------- 1001 1 1 1001 2 2 1001 3 3 */
#1的可以。换个思路的话,可以这样:declare @t table (lpz01 int,lpz03 int,lpz04 int) insert into @t select 1001,1,3;with maco as ( select lpz01,lpz03,lpz03 as lpz04 from @t union all select a.lpz01,a.lpz03+1,a.lpz03+1 from maco a,@t b where a.lpz03<b.lpz04 ) select * from maco /* lpz01 lpz03 lpz04 ----------- ----------- ----------- 1001 1 1 1001 2 2 1001 3 3 */
哈哈,你们都是高手,我在oracle数据库执行会报错啊
---------------------------------------------------------------- -- Author :TravyLee(物是人非事事休,欲语泪先流!) -- Date :2012-11-22 09:22:43 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[test] if object_id('[test]') is not null drop table [test] go create table [test] ( [lpz01] int, [lpz03] int, [lpz04] int ) insert [test] select 1001,1,3 go select [lpz01], a.number as [lpz03], a.number as [lpz04] from [test],master..spt_values a where a.number between [lpz03] and [lpz04] and a.type='p'/* lpz01 lpz03 lpz04 ----------- ----------- ----------- 1001 1 1 1001 2 2 1001 3 3(3 行受影响) */ 写完了才发现一楼跟我一个思路
DECLARE @t TABLE(a INT,b INT,c int)
INSERT INTO @t SELECT 1001,1,3;SELECT a,b.number AS b,b.number AS c FROM @t a JOIN master..spt_values b ON b.number BETWEEN a.b AND a.c AND b.type='p'/*
a b c
----------- ----------- -----------
1001 1 1
1001 2 2
1001 3 3
*/
insert into @t
select 1001,1,3;with maco as
(
select lpz01,lpz03,lpz03 as lpz04 from @t
union all
select a.lpz01,a.lpz03+1,a.lpz03+1 from maco a,@t b where a.lpz03<b.lpz04
)
select * from maco
/*
lpz01 lpz03 lpz04
----------- ----------- -----------
1001 1 1
1001 2 2
1001 3 3
*/
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-22 09:22:43
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[lpz01] int,
[lpz03] int,
[lpz04] int
)
insert [test]
select 1001,1,3
go
select
[lpz01],
a.number as [lpz03],
a.number as [lpz04]
from
[test],master..spt_values a
where
a.number between [lpz03] and [lpz04]
and a.type='p'/*
lpz01 lpz03 lpz04
----------- ----------- -----------
1001 1 1
1001 2 2
1001 3 3(3 行受影响)
*/
写完了才发现一楼跟我一个思路