27,579
社区成员
发帖
与我相关
我的任务
分享
create table #test(
username nvarchar(100),
companyname nvarchar(200),
jointime datetime2(7)
)
insert into #test(username, companyname, jointime)
select 'test1','测试1', '2018-01-03 00:00:00.0000000' union
select 'test2','测试2', '2017-01-03 00:00:00.0000000' union
select 'test3','测试3', '2016-01-03 00:00:00.0000000'
Insert into #test
SELECT 'admin','天津奥斯顿科技有限公司','2018-01-03 00:00:00.0000000' union all
select N'test1',N'天津重工','2017-01-06 00:00:00.0000000' union all
select N'test2',N'我是第而非大使馆','2018-01-06 00:00:00.0000000' union all
select N'f0001',N'企业一','2011-08-18 00:00:00.0000000' union all
select N'2018001',N'天津汇海科技','2018-01-19 00:00:00.0000000' union all
select N'test5',N'1112','2018-01-20 00:00:00.0000000' union all
select N'f0002',N'f0002','2013-02-21 00:00:00.0000000' union all
select N'test6',N'11212','2018-01-24 00:00:00.0000000'
GO
--测试数据结束
SELECT a.username,
a.companyname,
YEAR(a.jointime) + b.number AS jointime
FROM #test a,
[master].dbo.spt_values b
WHERE b.[type] = 'p'
AND YEAR(a.jointime) + b.number <= YEAR(GETDATE())
SELECT a.username ,
a.companyname ,
YEAR(b.jointime) AS jointime
FROM #test a
JOIN #test b ON a.jointime <= b.jointime
ORDER BY a.username
create table #test(
username nvarchar(100),
companyname nvarchar(200),
jointime datetime2(7)
)
insert into #test(username, companyname, jointime)
select 'test1','测试1', '2018-01-03 00:00:00.0000000' union
select 'test2','测试2', '2017-01-03 00:00:00.0000000' union
select 'test3','测试3', '2016-01-03 00:00:00.0000000'
--测试数据结束
SELECT a.username,a.companyname,YEAR(a.jointime) AS jointime
FROM #test a
INNER JOIN #test b ON a.jointime<=b.jointime
ORDER BY username
SELECT b.* FROM #test a JOIN #test b ON a.jointime<=b.jointime