初始值是
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
刚才发帖未解决就结贴了，现重新开，请大神帮助！！
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tb')BEGIN DROP TABLE tbENDGOcreate table tb(col1 int,col2 int,col3 varchar(10))insert into tb select 1,1,'a' unionselect 1,2,'b' unionselect 2,4,'c' select a.col1,b.number,a.col3from 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'UNIONselect a.col1,b.number,a.col3from (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 > 0col1 number col31 1 a1 2 b1 3 b2 1 c2 2 c2 3 c2 4 c
------解决方案--------------------
引用http://topic.csdn.net/u/20120614/16/adb230c1-205e-41cb-9356-744d07ccef5f.html#r_78865191
- SQL code
--完整如下: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 allselect 1,3,23 union allselect 2,7,24--------------开始查询--------------------------select line,row_number()over(partition by line order by line),serialfrom[tb] aleft joinmaster..spt_values con c.number<=a.[count]and c.number>0and c.[type]='p'