解决方案 »
- 查询后小数点不要显示。
- 怎样实现在数据表中存储大量的数据,急啊
- 数据库备份还原的问题
- 如何把一个字符串转为语句
- select * from 社团,为何在access中间正确执行,在sql中间有误
- sql7如果升级到sql2000会产生什么问题啊?请进。
- SqlDumpExceptionHandler: 进程 55 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION。SQL Server 将终止该进程。
- 一个多表查询与统计的问题?
- 小康问题:set xact_abort on指令为什么没作用?
- 求个问题
- ups供电,有没有办法在停电开始的时候,电脑自动开始关机?
- 代码设计
(
shipID varchar(10),
sessionID varchar(10),
EndDate_N varchar(20),
EndDate_S varchar(20),
EndDate_F varchar(20),
EndDate_X varchar(20)
)
drop table test1insert test1(shipID,sessionID,EndDate_N,EndDate_S,EndDate_F,EndDate_X)
select '1','101','2010-05-06','2010-7-20','2010-7-22','2010-7-21'union
select '1','102','2010-05-06','2010-7-20','2010-7-22',null union
select '2','102','2010-05-06','2010-7-20','2010-7-21',null union
select '3','102','2010-10-06','2010-7-20',null,nullselect shipid,sessionid,convert(varchar(10),max(cast(enddate_n as datetime)),121) from (
select shipid,sessionid,enddate_n from test1 union all
select shipid,sessionid,enddate_s from test1 union all
select shipid,sessionid,enddate_f from test1 union all
select shipid,sessionid,enddate_x from test1 ) a
group by shipid,sessionid/*
1 101 2010-07-22
1 102 2010-07-22
2 102 2010-07-21
3 102 2010-10-06
*/
;WITH cte
AS
(
SELECT shipid,sessionid,[date]
FROM #test t
UNPIVOT( [date] FOR EndDate IN([EndDate_N],[EndDate_S],[EndDate_F],[EndDate_X]))upvt
)
SELECT shipid,sessionid,EndDate
FROM
(
SELECT shipid,sessionid,EndDate =MAX([date])
FROM cte c
GROUP BY shipid,sessionid
)M
ORDER BY left(EndDate,7),RIGHT(EndDate,2)DESCshipid sessionid EndDate
---------- ---------- --------------------
1 101 2010-07-22
1 102 2010-07-22
2 102 2010-07-21
3 102 2010-10-06(4 row(s) affected)
(
shipID varchar(10),
sessionID varchar(10),
EndDate_N varchar(20),
EndDate_S varchar(20),
EndDate_F varchar(20),
EndDate_X varchar(20)
)
insert test(shipID,sessionID,EndDate_N,EndDate_S,EndDate_F,EndDate_X)
select '1','101','2010-05-06','2010-7-20','2010-7-22','2010-7-21'union
select '1','102','2010-05-06','2010-7-20','2010-7-22',null union
select '2','102','2010-05-06','2010-7-20','2010-7-21',null union
select '3','102','2010-10-06','2010-7-20',null,nullselect shipID,sessionID,max(EndDate) EndDate from
(
select shipID,sessionID,convert(varchar(10),cast(EndDate_N as datetime),120) EndDate from test where EndDate_N is not null
union all
select shipID,sessionID,convert(varchar(10),cast(EndDate_S as datetime),120) EndDate from test where EndDate_S is not null
union all
select shipID,sessionID,convert(varchar(10),cast(EndDate_F as datetime),120) EndDate from test where EndDate_F is not null
union all
select shipID,sessionID,convert(varchar(10),cast(EndDate_X as datetime),120) EndDate from test where EndDate_X is not null
) t
group by shipID,sessionID drop table test/*
shipID sessionID EndDate
---------- ---------- ----------
1 101 2010-07-22
1 102 2010-07-22
2 102 2010-07-21
3 102 2010-10-06(所影响的行数为 4 行)
*/