--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (Name varchar(4))
insert into #1
select 'abc1' union all
select 'abc2' union all
select 'abc3' union all
select 'abc4' union all
select 'abc5' union all
select 'abc6' union all
select 'abc7'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (Day varchar(2),Num int)
insert into #2
select 'd1',2 union all
select 'd2',1 union all
select 'd3',4select a.Name, b.Day from #1 a, #2 b where right(Name,1)<=b.Num
/*
Name Day
---- ----
abc1 d1
abc2 d1
abc1 d2
abc1 d3
abc2 d3
abc3 d3
abc4 d3
*/
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (Name varchar(4))
insert into #1
select 'abc1' union all
select 'abc2' union all
select 'abc3' union all
select 'abc4' union all
select 'abc5' union all
select 'abc6' union all
select 'abc7'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (Day varchar(2),Num int)
insert into #2
select 'd1',2 union all
select 'd2',1 union all
select 'd3',4select a.Name, b.Day from #1 a, #2 b where right(Name,1)<=b.Num
/*
Name Day
---- ----
abc1 d1
abc2 d1
abc1 d2
abc1 d3
abc2 d3
abc3 d3
abc4 d3
*/
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (Name varchar(4))
insert into #1
select 'abc1' union all
select 'abc2' union all
select 'abc3' union all
select 'abc4' union all
select 'abc5' union all
select 'abc6' union all
select 'abc7'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (Day varchar(2),Num int)
insert into #2
select 'd1',2 union all
select 'd2',1 union all
select 'd3',4if object_id('tempdb.dbo.#t1') is not null drop table #t1
select id=identity(int,1,1),* into #t1 from #1if object_id('tempdb.dbo.#t2') is not null drop table #t2
select id=identity(int,1,1),b.Day into #t2 from #t1 a, #2 b where a.id<=b.Num order by b.dayselect a.Name,b.Day from #t1 a, #t2 b where a.id=b.id/*
Name Day
---- ----
abc1 d1
abc2 d1
abc3 d2
abc4 d3
abc5 d3
abc6 d3
abc7 d3
*/
/******************************************************************************/
/*回复:20080521005总:00041 */
/*主题:一次赋值 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @a table([Name] varchar(4))
insert into @a select 'abc1'
insert into @a select 'abc2'
insert into @a select 'abc3'
insert into @a select 'abc4'
insert into @a select 'abc5'
insert into @a select 'abc6'
insert into @a select 'abc7'
declare @b table([Day] varchar(2),[Num] int)
insert into @b select 'd1',2
insert into @b select 'd2',1
insert into @b select 'd3',4--代码--------------------------------------------------------------------------
select id=identity(int,1,1),* into # from @a
select id=identity(int,1,1),b=num,e=0,d=[day] into #1 from @b
update a set e=(select sum(b) from #1 where id<=a.id) from #1 a
update #1 set b= e-b+1
select a.name,b.d from # a,#1 b where a.id between b.b and b.e
drop table #,#1
go/*结果--------------------------------------------------------------------------
name d
---- ----
abc1 d1
abc2 d1
abc3 d2
abc4 d3
abc5 d3
abc6 d3
abc7 d3
--清除------------------------------------------------------------------------*/
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (Name varchar(4))
insert into #1
select 'abc1' union all
select 'abc2' union all
select 'abc3' union all
select 'abc4' union all
select 'abc5' union all
select 'abc6' union all
select 'abc7'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (Day varchar(2),Num int)
insert into #2
select 'd1',2 union all
select 'd2',1 union all
select 'd3',4select id=identity(int,1,1),* into # from #1select name,b.day
from # a left join
(select day,maxnum=(select sum(num) from #2 where day<=a.day),minnum=(select isnull(sum(num),0) from #2 where day<a.day) from #2 a) b
on a.id<=b.maxnum and a.id>b.minnum