本系列将收藏或者是转载MS SQL的一些技巧,包括查询或者是数据库设计、优化等。
本文来源:求连续三年获得优秀奖的人
原理就是从最小的年份开始,将连续三年(使用最小年份加1和加2实现)的表进行inner join on 年份=年份+1 and 年份=年份+2 and 姓名相同,然后再最后join出来的表中就是连续三年的数据了
MSSQL如下
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->
Create
table
LastThreeYear
(
PKID int not null identity ( 1 , 1 ),
TYear int not null ,
TName Nvarchar ( 20 )
)
go
insert into LastThreeYear(TYear,TName) values ( 2005 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2006 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2007 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2007 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 令狐不冲 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 令狐不冲 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 大刀王五 ' )
go
select * from LastThreeYear where 1 = 1
go
select MIN (t.TYear) as MinYear,t.TName
from LastThreeYear t
inner join LastThreeYear y on t.TYear + 1 = y.TYear and t.TName = y.TName
inner join LastThreeYear x on t.TYear + 2 = x.TYear and t.TName = x.TName
where 1 = 1
group by t.TName
go
(
PKID int not null identity ( 1 , 1 ),
TYear int not null ,
TName Nvarchar ( 20 )
)
go
insert into LastThreeYear(TYear,TName) values ( 2005 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2006 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2007 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2007 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 大刀王五 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2008 , ' 令狐不冲 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 胡一刀 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 令狐不冲 ' )
insert into LastThreeYear(TYear,TName) values ( 2009 , ' 大刀王五 ' )
go
select * from LastThreeYear where 1 = 1
go
select MIN (t.TYear) as MinYear,t.TName
from LastThreeYear t
inner join LastThreeYear y on t.TYear + 1 = y.TYear and t.TName = y.TName
inner join LastThreeYear x on t.TYear + 2 = x.TYear and t.TName = x.TName
where 1 = 1
group by t.TName
go
结果如下
MinYear TName
2006 大刀王五
2007 胡一刀