比如我们查询出来的是31 号 和30号这两个日期,因为是排假期,既然两天都是假期,那么肯定是算31号到期,那么怎么才能显示最大的那个而不是小的,比如8月1号肯定比7月31大我原来语句是这样的
select NAME as 姓名,DEPTNAME as 部门,CONVERT(varchar(10) ,ENDSPECDAY, 111 ) as 假期到期时间,LeaveName as 假类 from USERINFO,DEPARTMENTS,USER_SPEDAY,LeaveClass where (CONVERT(varchar(10) ,DateAdd(DAY,-1,ENDSPECDAY), 111 )= CONVERT(varchar(10) ,GETDATE(), 111 ) or CONVERT(varchar(10) ,ENDSPECDAY, 111 )= CONVERT(varchar(10) ,GETDATE(), 111 )) and USERINFO.DEFAULTDEPTID=DEPARTMENTS.DEPTID and dbo.USER_SPEDAY.USERID=USERINFO.USERID and LeaveClass.LeaveId=USER_SPEDAY.DATEID order by 假期到期时间 desc
------解决方案--------------------
select *
from(select NAME as 姓名,DEPTNAME as 部门,CONVERT(varchar(10) ,ENDSPECDAY, 111 ) as 假期到期时间,LeaveName as 假类,
row_number()over(partition by name order by ENDSPECDAY desc ) id
from USERINFO,DEPARTMENTS,USER_SPEDAY,LeaveClass where ...) a
where a.id=1