--字母列 val 数字列 numselect a.val from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and a.num
select a.val from tb a,master..spt_values b where b.[type] = 'p' and b.number<a.num
希望补充下问题,还望解答: 理想输出为: 1 A 2 A 3 A 1 B 2 B 1 C 2 C 3 C 4 C
select b.number+1,a.val from tb a,master..spt_values b where b.[type] = 'p' and b.number<a.num
补充的那个问题,有点问题,重新发下,还望大侠们给予指导: 求一SQL语句 例如表数据为: A A A B B C C C C理想输出为: 1 A 2 A 3 A 1 B 2 B 1 C 2 C 3 C 4 C
declare @t table(v varchar(10),t int); insert into @t select 'a',3 union all select 'b',2 union all select 'c',4select * from @t a join master..spt_values b on a.t>b.number and b.type='p'
select num=row_number()over(partition by val order by getdate()),* from ( select a.val from tb a,master..spt_values b where b.[type] = 'p' and b.number<a.num)t
select val,b=row_number() over(partition by val order by @@rowcount) from tb
--字母列 val 数字列 numselect a.val
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and a.num
where b.[type] = 'p' and b.number<a.num
理想输出为:
1 A
2 A
3 A
1 B
2 B
1 C
2 C
3 C
4 C
where b.[type] = 'p' and b.number<a.num
求一SQL语句
例如表数据为:
A
A
A
B
B
C
C
C
C理想输出为:
1 A
2 A
3 A
1 B
2 B
1 C
2 C
3 C
4 C
declare @t table(v varchar(10),t int);
insert into @t select 'a',3 union all select 'b',2 union all select 'c',4select * from @t a join master..spt_values b on a.t>b.number and b.type='p'
(
select a.val from tb a,master..spt_values b
where b.[type] = 'p' and b.number<a.num)t
select val,b=row_number() over(partition by val order by @@rowcount) from tb