数据行数 转换问题
我现在 有 A表 字段 ID name RowNumber
数据 1 ij 3
2 as 4
3 asd 1
能用什么方法 实现 根据 rowNumber 变成 RowNumber行的 数据
就想 这样的 ID 等于 1 的 rowNumber = 3 就是 数据变成 三条
1 ij 3
1 ij 3
1 ij 3
2 as 4
2 as 4
2 as 4
2 as 4
3 asd 1
[最优解释]
-------------以下是测试数据-----------------:
use Tempdb
go
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
create table [#1](ID int, name varchar(3), RowNumber int)
insert [#1]
select 1,'ij',3 union all
select 2,'as',4 union all
select 3,'asd',1
--------------开始查询--------------------------
select ID,a.name,c.number
from
[#1] a
left join
master..spt_values c
on
c.number<=a.[RowNumber]
and
c.number>0
and
c.[type]='p'
-------------------------------------------------
--效果如下
/*
IDnamenumber
1ij1
1ij2
1ij3
2as1
2as2
2as3
2as4
3asd1
*/
with tb(ID, name,RowNumber )
as(
select 1,'ij',3 union all
select 2,'as',4 union all
select 3,'asd',1)
select tb.* from tb,master..spt_values a where a.type='p'
and number between 1 and RowNumber
-------------以下是测试数据-----------------:
use Tempdb
go
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
create table [#1](ID int, name varchar(3), RowNumber int)
insert [#1]
select 1,'ij',3 union all
select 2,'as',4 union all
select 3,'asd',1
--------------开始查询--------------------------
select ID,a.name,a.[RowNumber]
from
[#1] a
left join
master..spt_values c
on
c.number<=a.[RowNumber]
and
c.number>0
and
c.[type]='p'
-------------------------------------------------
--效果如下
/*
IDnameRowNumber
1ij3
1ij3
1ij3
2as4
2as4
2as4
2as4
3asd1
*/
if(object_id('a') is not null)drop table a
go
create table a
(
id int,
[name] varchar(5),
num int
)
go
insert into a
select 1,'ij',3 union all
select 2,'as',4 union all
select 3,'asd',1
go
select a.* from a left join master..spt_values as m on m.number <= a.num and m.number >0 and m.type = 'p'
/*
id name num
----------- ----- -----------
1 ij 3
1 ij 3
1 ij 3
2 as 4
2 as 4
2 as 4
2 as 4
3 asd 1
(8 行受影响)
*/
