求其他系中比信管系某一学生年龄小的学生信息(即求其它系中年龄小于信管系年龄最大者的学生)
--方法一:非信管专业学生与信管学生进行笛卡尔积,先比较年份,年分相同比较月份,年份月份相同,比较日子,筛选出符合条件的信息
select distinct m1.S#,m1.Sname,m1.Ssex,m1.Sbirth,m1.Sclass, m1.D#
from (select * from Student where D# not in (select D# from Dept where Dname='信管专业'))as m1,
(select * from Student where D# in (select D# from Dept where Dname='信管专业'))as m2
Where year(m1.Sbirth)>year(m2.Sbirth) or year(m1.Sbirth)=year(m2.Sbirth) and month(m1.Sbirth)>month(m2.Sbirth)or year(m1.Sbirth)=year(m2.Sbirth) and month(m1.Sbirth)=month(m2.Sbirth) andday(m1.Sbirth)>day(m2.Sbirth)
方法二:直接利用Sbirth比较大小
select * from Student where D# not in (select D# from Dept where Dname='信管专业') and Sbirth>(select top 1 Sbirth from Student where D# in(select D# from Dept where Dname='信管专业') order by Sbirth )
出生日期越小,年龄越大,即Sbirth越小大于最小的出生日期就是小于年龄最大的同学
方法三:利用函数---DATEDIFF(datepart,startdate,enddate)
datepart 日期或时间部分的名称(或缩写)。可以用大写或小写来指定,有或没有引号。startdate - 间隔的开始日期/时间。可以是各种标准格式的日期、时间或日期时间。enddate - 间隔的结束日期/时间。可以是各种标准格式的日期、时间或日期
从enddate中减去startdate,以确定两个日期之间的日期部分间隔。DATEDIFF函数返回两个指定日期之间指定日期部分差的整数。日期范围从开始日期开始,到结束日期结束。(如果enddate早于startdate,DATEDIFF将返回一个负整数值。)
DATEDIFF返回startdate和enddate之间指定单位的总数。
Datepart 参数日期部分参数可以是下列日期/时间组件之一,可以是全名(日期部分列)或其缩写(缩写列)。这些datepart组件名称和缩写不区分大小写。
weekday和dayofyear datepart值在功能上与day datepart值相同。
对于上面单位确定以后,它是只取到该单位,比他单位级别小的就不再取例如,例如,指定连续年份的任意两个日期(例如2018-09-23和2019-01-01)返回的年份DATEDIFF为1,而不管这两个日期之间的实际持续时间是大于还是小于365天。同样,10:23:58和10:24:05之间的分钟数是1,尽管实际上只有7秒将两个值分开。获取当期时间GETDATE()
-------max-------------
select *from Student S
where D# not in (select D# from Dept where Dname='信管专业')and (DATEDIFF(dd,Sbirth,GETDATE()))<(select max (DATEDIFF(dd,Sbirth,GETDATE())) from Student S where D# in (select D# from Dept where Dname='信管专业'))
---------top 1---------
select *from Student S
where D# not in (select D# from Dept where Dname='信管专业')and (DATEDIFF(dd,Sbirth,GETDATE()))<(select top 1 DATEDIFF(dd,Sbirth,GETDATE()) from Student S where D# in (select D# from Dept where Dname='信管专业')order by DATEDIFF(dd,Sbirth,GETDATE()) desc)
--top k 列名或其他 :
用之前要排序
--怎样精确的表示年龄,除过365后余数怎么处理?
单位细分:出生几年零几个月零几天计算年龄:
select DATEDIFF(dd,'2004-08-11',GETDATE()) select DATEDIFF(dd,'2004-08-11',GETDATE())----多少天 select DATEDIFF(dd,'2004-08-11',GETDATE())/365---几年 select DATEDIFF(dd,'2004-08-11',GETDATE())%365 ---剩几天select DATEDIFF(dd,'2004-08-11',GETDATE())%365/30--零几月 select DATEDIFF(dd,'2004-08-11',GETDATE())%365/30%30 零几天
--19岁零两个月零两天
--考虑到一个月可能30天也可能31天 select 0.5*(DATEDIFF(dd,'2004-08-11',GETDATE())%365/30+DATEDIFF(dd,'2004-08-11',GETDATE())%365/31)----零几个月
select 0.5*( DATEDIFF(dd,'2004-08-11',GETDATE())%365/30%30+DATEDIFF(dd,'2004-08-11',GETDATE())%365/31%31)---零几天--19岁零两个月零两天,
--多次实验,结果都相差不大,就直接按30算--猜测,也许可以循环一年一年算,最后算总和,太麻烦
计算有多少个月,用月数去整除12
select DATEDIFF(mm,'2004-08-11',GETDATE())--230个月
select DATEDIFF(mm,'2003-12-11',GETDATE())--238个月
select DATEDIFF(mm,'2004-08-11',GETDATE())/12--19岁
select DATEDIFF(mm,'2003-12-11',GETDATE())/12--19岁
计算年数,比较当前月份与出生月份,若生日月份小于当前月份的,年龄即所求年数
若生日月份大于当前月份,年龄即所求年数减一
例子
select DATEDIFF(yy,'2003-12-11',GETDATE())-----20岁
if (month('2003-12-11')-month(getdate())<0)
select DATEDIFF(yy,'2003-12-11',GETDATE())
if (month('2003-12-11')-month(getdate())>=0)
select DATEDIFF(yy,'2003-12-11',GETDATE())-1-------19岁
例子
select DATEDIFF(yy,'2004-08-11',GETDATE())------19岁
if (month('2004-08-11')-month(getdate())<0)
select DATEDIFF(yy,'2004-08-11',GETDATE())
if (month('2004-08-11')-month(getdate())>=0)
select DATEDIFF(yy,'2004-08-11',GETDATE())-1------19岁