34,597
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [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
*/
SELECT [shipID] ,[sessionID] ,max([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]
select shipID ,sessionID,
case when EndDate_N >= EndDate_S and EndDate_N >= EndDate_F and EndDate_N >= EndDate_X then EndDate_N
when EndDate_S >= EndDate_D and EndDate_S >= EndDate_F and EndDate_S >= EndDate_X then EndDate_S
when EndDate_F >= EndDate_N and EndDate_F >= EndDate_S and EndDate_F >= EndDate_X then EndDate_F
when EndDate_X >= EndDate_N and EndDate_X >= EndDate_S and EndDate_X >= EndDate_F then EndDate_X
end EndDate
from tb
--->测试脚本
create table Union_Test
(
shipID varchar(10),
sessionID varchar(10),
EndDate_N varchar(20),
EndDate_S varchar(20),
EndDate_F varchar(20),
EndDate_X varchar(20)
)
insert into Union_Test
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','' union all
select '2','102','2010-05-06','2010-7-20','2010-7-21','' union all
select '3','102','2010-10-06','2010-7-20','',''
--->执行语句
select shipID,sessionID,MAX(EndDate_N) as [MAX] from
(
select shipID,sessionID,EndDate_N from Union_Test
union all
select shipID,sessionID,EndDate_S from Union_Test
union all
select shipID,sessionID,EndDate_F from Union_Test
union all
select shipID,sessionID,EndDate_X from Union_Test
) as U
group by shipID,sessionID
--->清除数据
drop table Union_Test
--->结果
shipID sessionID MAX
---------- ---------- --------------------
1 101 2010-7-22
1 102 2010-7-22
2 102 2010-7-21
3 102 2010-7-20
(4 行受影响)
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 ,cast(EndDate_N as datetime) as EndDate_N from TB
union all
select shipID ,sessionID ,cast(EndDate_S as datetime) from TB
union all
select shipID ,sessionID ,cast(EndDate_F as datetime) from TB
union all
select shipID ,sessionID ,cast(EndDate_X as datetime) from TB
) as tt
group by shipID ,sessionID