请问怎样得到这个结果？？？

[ignacio2222]

初始值是
1 1 a
1 2 b
2 4 c

想要得到的结果：
1 1 a
1 2 b
1 3 b
2 1 c
2 2 c
2 3 c
2 4 c

刚才发帖未解决就结贴了，现重新开，请大神帮助！！

[快溜]

create table tb(col1 int,col2 int,col3 varchar(10))
insert into tb
 select 1,1,'a' union
select 1,2,'b' union
select 2,4,'c' 

select a.col1,b.number+1,a.col3
from tb a,spt_values b where a.col2>b.number
and b.type='p'

/*
col1                    col3
----------- ----------- ----------
1           1           a
1           1           b
1           2           b
2           1           c
2           2           c
2           3           c
2           4           c

(7 行受影响)

[ignacio2222]

跟想要得到的结果不一样呀，有这么难啊？

[ignacio2222]

有大神能解决下吗？

[ignacio2222]

[Quote=引用 1 楼 的回复:]
SQL code

create table tb(col1 int,col2 int,col3 varchar(10))
insert into tb
select 1,1,'a' union
select 1,2,'b' union
select 2,4,'c'

select a.col1,b.number+1,a.col3
from tb a,spt_values b……
[/Quote]
第二列开头那里是123，不是112啊，

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tb')
BEGIN
	DROP TABLE tb
END
GO
create table tb(col1 int,col2 int,col3 varchar(10))
insert into tb
 select 1,1,'a' union
select 1,2,'b' union
select 2,4,'c' 

select a.col1,b.number,a.col3
from tb a,master..spt_values b 
where a.col2 <= b.number and number < (select MIN(col2) from tb where col3 > a.col3) and b.type='p'
UNION
select a.col1,b.number,a.col3
from (SELECT rn=ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2),col1,col2,col3 FROM tb) a,master..spt_values b 
where b.type='p' AND rn = 1 AND number <= col2 AND number > 0

col1	number	col3
1	1	a
1	2	b
1	3	b
2	1	c
2	2	c
2	3	c
2	4	c

[还在加载中灬]

--修改小F大大的
select line,row_number()over(partition by line order by line),serial
from
[tb]a
left join
master..spt_values c
on
   c.number<=a.[count]
and
   c.number>0
and
   c.[type]='p'

[还在加载中灬]

引用http://topic.csdn.net/u/20120614/16/adb230c1-205e-41cb-9356-744d07ccef5f.html#r_78865191

--完整如下:
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([line] int,[count] int,[serial] int)
insert [tb]
select 1,2,22 union all
select 1,3,23 union all
select 2,7,24

--------------开始查询--------------------------
select line,row_number()over(partition by line order by line),serial
from
[tb] a
left join
master..spt_values c
on
   c.number<=a.[count]
and
   c.number>0
and
   c.[type]='p'

[ignacio2222]

谢谢各位！！7楼的是正解，5楼的换个数据就有点不对了

[gal1024]

这数据有什么关系？