用select 能不能按一条记录中的 数量 字段的值列出数量值的行数?比如create table t1 (a1 varchar(10),a2 varchar(10),数量 int)insert into t1 values('aa','bb',2)
insert into t1 values('cc','ff',3)用select 可不可以做得到下面的结果
a1 a2
---------------------
aa bb
aa bb
cc ff
cc ff
cc ff
insert into t1 values('cc','ff',3)用select 可不可以做得到下面的结果
a1 a2
---------------------
aa bb
aa bb
cc ff
cc ff
cc ff
insert into t1 values('cc','ff',3) go
select Top 50 ID=Identity(int,1,1) into # from sysobjects--生成递增辅助表
select
a1,a2
from
t1
join
# t2 on t1.数量!<t2.IDa1 a2
---------- ----------
aa bb
aa bb
cc ff
cc ff
cc ff(所影响的行数为 5 行)
create table t1 (a1 varchar(10),a2 varchar(10),数量 int) insert into t1 values('aa','bb',2)
insert into t1 values('cc','ff',3) select * from t1select top 100 id=identity(int,1,1) into # from syscolumns select a1,a2
from t1,#
where #.id<=数量/*
a1 a2
---------- ----------
aa bb
aa bb
cc ff
cc ff
cc ff(所影响的行数为 5 行)
*/
create table t1 (a1 varchar(10),a2 varchar(10), qty int) insert into t1 values('aa','bb',2)
insert into t1 values('cc','ff',3)
declare @aa varchar(8000)
declare @a1a2 varchar(20)
declare @i int
set @aa=''
DECLARE abc CURSOR FOR
SELECT distinct a1+a2 FROM t1
OPEN abc
FETCH NEXT FROM abc into @a1a2
WHILE @@FETCH_STATUS = 0
BEGIN
select @i=qty from t1 where a1+a2=@a1a2
set @aa=@aa+REPLICATE('union all select * from t1 where a1+a2='''+@a1a2+'''',@i)
print @aa
FETCH NEXT FROM abc into @a1a2
END
CLOSE abc
DEALLOCATE abc
set @aa=right (@aa,len(@aa) - 9)
print @aa
exec(@aa)aa bb 2
aa bb 2
cc ff 3
cc ff 3
cc ff 3