取最大的日期

最新推荐文章于 2025-02-09 21:50:44 发布

sostemp

最新推荐文章于 2025-02-09 21:50:44 发布

阅读量645

点赞数

分类专栏： SQL 文章标签： 2010 null table insert 测试 object

SQL 专栏收录该内容

35 篇文章

订阅专栏

字段
shipID, varchar(10)
sessionID, varchar(10)
EndDate_N, varchar(20)
EndDate_S, varchar(20)
EndDate_F, varchar(20)
EndDate_X, varchar(20)
以为项目开发需要 EndDate_N 下面这几个字段我转换为varchar存进去的.
测试数据
1 101 2010-05-06 2010-7-20 2010-7-22 2010-7-21
1 102 2010-05-06 2010-7-20 2010-7-22
2 102 2010-05-06 2010-7-20 2010-7-21
3 102 2010-10-06 2010-7-20

想查出来 1 101 2010-7-22
1 102 2010-7-22
1 102 2010-7-21
4 102 2010-10-06 其实就是取最大的日期
新人求助

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table tb(shipID varchar(10),sessionID varchar(10),EndDate_N varchar(20),EndDate_S varchar(20),EndDate_F varchar(20),EndDate_X varchar(20))
insert into [tb]
select 1,101,'2010-05-06','2010-7-20','2010-7-22','2010-7-21' union all
select 1,102,'2010-05-06','2010-7-20','2010-7-22',null union all
select 2,102,'2010-05-06','2010-7-20','2010-7-21',null union all
select 3,102,'2010-10-06','2010-7-20',null,null

SELECT [shipID] ,[sessionID] ,max([EndDate_N]) as [EndDate_N]   FROM
(
select [shipID] ,[sessionID] ,[EndDate_N] as [EndDate_N] from TB
union all
select [shipID] ,[sessionID] ,[EndDate_S] from TB
union all
select [shipID] ,[sessionID] ,[EndDate_F] from TB
union all
select [shipID] ,[sessionID] ,[EndDate_X] from TB
)[tt]
group by [shipID] ,[sessionID]

/*
shipID    sessionID    EndDate_N
1    101    2010-7-22
1    102    2010-7-22
2    102    2010-7-21
3    102    2010-7-20
*/