求连续三年获得优秀奖的人
0
悬赏园豆：10
[已关闭问题]
ID 自增
Year 年份
Name 姓名
查询连续三年被评为优秀干部人的姓名， 年份格式为 2009 ，2008，2007
请问SQL 怎么写？
其他回答(3)
0
SELECT [Name] FROM [Table] a WHERE EXISTS(SELECT NULL FROM [Table] b WHERE a.[Year]-1=b.[Year] AND a.[Name]=b.[Name]) AND EXISTS(SELECT NULL FROM [Table] c WHERE a.[Year]+1=c.[Year] AND a.[Name]=c.[Name])
随便写写，好像效率不咱的。。。
多谢阿松，但是你的答案是错误的。
这个答案的思路肯定是对的
0
一个简单示例，楼主可以测试：
代码
Create table LastThreeYear
(PKID int not null identity(1,1)
,TYear int not null
,TName Nvarchar(20)
)
go
insert into LastThreeYear
select 2007,'大刀王五'
union all select 2008,'大刀王五'
union all select 2008,'胡一刀'
union all select 2009,'大刀王五'
union all select 2006,'大刀王五'
union all select 2005,'胡一刀'
union all select 2007,'胡一刀'
union all select 2009,'令狐不冲'
union all select 2008,'令狐不冲'
select * from LastThreeYear
where 1=1
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
MinYear TName
2006 大刀王五
多谢！ 我自己的方法比较笨拙。
0
原理就是从最小的年份开始，将连续三年（使用最小年份加1和加2实现）的表进行inner join on 年份=年份+1 and 年份=年份+2 and 姓名相同，然后再最后join出来的表中就是连续三年的数据了
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
2006 大刀王五
2007 胡一刀
0
最容易想到的就是如下写法
SQL 直译需求
select
*
from
LastThreeYear a
where
exists
(
select
1
from
LastThreeYear
where
tyear = a.tyear + 1
and tname = a.tname
)
and
exists
(
select
1
from
LastThreeYear
where
tyear = a.tyear + 2
and tname = a.tname
)
这道题另一个经典解法就是"双not exists",
面试经典,性能就不要考虑了
下面的写法比较通用,参数化了连续天数
declare @ int
set @ = 3
select
*
from
LastThreeYear a
where
not exists
(
select
1
from
(
select
Top (@)
*
from
(
select 0 as id
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) T
) TT
where
not exists
(
select
1
from
LastThreeYear
where
tyear = a.tyear + tt.id
)
)
order by
a.tname, a.tyear
