有表 Friend,存在id birthday这些字段
如下
id Brithday
1 1936-4-6
2 1986-4-16
3 1989-1-6
4 1986-3-6
5 1986-8-6
6 1981-4-13
7 1985-2-1
8 1986-4-19
我想知道最近一周内,也就是从今天数七天之内哪些人有过生日的。
sql怎么写?
如下
id Brithday
1 1936-4-6
2 1986-4-16
3 1989-1-6
4 1986-3-6
5 1986-8-6
6 1981-4-13
7 1985-2-1
8 1986-4-19
我想知道最近一周内,也就是从今天数七天之内哪些人有过生日的。
sql怎么写?
insert into tb values(1 , '1936-4-6')
insert into tb values(2 , '1986-4-16')
insert into tb values(3 , '1989-1-6')
insert into tb values(4 , '1986-3-6')
insert into tb values(5 , '1986-8-6')
insert into tb values(6 , '1981-4-13')
insert into tb values(7 , '1985-2-1')
insert into tb values(8 , '1986-4-19')
goselect * from tb where datediff(day , datename(year,getdate()) + right(convert(varchar(10),Brithday,120),6), getdate()) between -7 and 7drop table tb/*
id Brithday
----------- ------------------------------------------------------
1 1936-04-06 00:00:00.000
2 1986-04-16 00:00:00.000
6 1981-04-13 00:00:00.000(所影响的行数为 3 行)
*/
insert ta select 1,'2001-01-01'
insert ta select 2,'2001-04-01'
insert ta select 3,'2001-04-11'
insert ta select 4,'2001-04-07'
insert ta select 5,'2001-04-09'
go
select *
from ta
where datepart(wk,dateadd(yy,datediff(yy,birthday,getdate()),birthday)) =
datepart(wk,getdate())drop table ta/*
id birthday
----------- ------------------------------------------------------
3 2001-04-11 00:00:00.000
4 2001-04-07 00:00:00.000
5 2001-04-09 00:00:00.000(所影响的行数为 3 行)
*/
insert into tb values(1 , '1936-4-6')
insert into tb values(2 , '1986-4-16')
insert into tb values(3 , '1989-1-6')
insert into tb values(4 , '1986-3-6')
insert into tb values(5 , '1986-8-6')
insert into tb values(6 , '1981-4-13')
insert into tb values(7 , '1985-2-1')
insert into tb values(8 , '1986-4-19')
go--今天起前后各七天
select * from tb where datediff(day , datename(year,getdate()) + right(convert(varchar(10),Brithday,120),6), getdate()) between -7 and 7
/*
id Brithday
----------- ------------------------------------------------------
1 1936-04-06 00:00:00.000
2 1986-04-16 00:00:00.000
6 1981-04-13 00:00:00.000(所影响的行数为 3 行)
*/--今天起之后七天
select * from tb where datediff(day , datename(year,getdate()) + right(convert(varchar(10),Brithday,120),6), getdate()) between -7 and 0
/*
id Brithday
----------- ------------------------------------------------------
2 1986-04-16 00:00:00.000
6 1981-04-13 00:00:00.000(所影响的行数为 2 行)
*/--今天起之前七天
select * from tb where datediff(day , datename(year,getdate()) + right(convert(varchar(10),Brithday,120),6), getdate()) between 0 and 7
/*
id Brithday
----------- ------------------------------------------------------
1 1936-04-06 00:00:00.000(所影响的行数为 1 行)
*/drop table tb
insert into ta values(1 , '1936-4-6')
insert into ta values(2 , '1986-4-16')
insert into ta values(3 , '1989-1-6')
insert into ta values(4 , '1986-3-6')
insert into ta values(5 , '1986-8-6')
insert into ta values(6 , '1981-4-13')
insert into ta values(7 , '1985-2-1')
insert into ta values(8 , '1986-4-19')
go
select *
from ta
where datediff(d,dateadd(yy,datediff(yy,birthday,getdate()),birthday),getdate()) between -7 and 0
drop table ta
/*id Birthday
----------- ------------------------------------------------------
2 1986-04-16 00:00:00.000
6 1981-04-13 00:00:00.000(所影响的行数为 2 行)
*/
sql语句功能好强大!