27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[CTable](
[CName] [varchar](50) NULL,
[CNum] [int] NULL
) ON [PRIMARY]
GO
insert into CTable VALUES('S19',3)
insert into CTable VALUES('S18',2)
insert into CTable VALUES('S17',2)
insert into CTable VALUES('S16',1)
declare @tb_T table
(
[CName] [varchar](10) NULL,
[CNum] [int] NULL
);
insert into @tb_T VALUES('S19',3)
insert into @tb_T VALUES('S18',2)
insert into @tb_T VALUES('S17',2)
insert into @tb_T VALUES('S16',1)
select t.[CName] from @tb_T t
inner join master..spt_values s on s.type ='L' and s.number < t.CNum
/*
CName
----------
S19
S19
S19
S18
S18
S17
S17
S16
*/
SELECT T1.*
FROM CTable T1
JOIN master..spt_values T2 ON T1.CNum>T2.number
WHERE T2.type='P'