sql查询表中的反向数据 [英] sql query for retrive data from table

问题描述

我有这种格式的表格



代码Bscid st_time状态

101 100010 01-June-2014 11:00 start

101 100010 01-June-2014 11:02 end

101 100010 01-June-2014 11:04 start

101 100010 01-June- 2014 11:06结束

101 100010 01-June-2014 11:08 start





Out Put格式





代码Bscid st_time end_time状态

101 100010 01-June-2014 11:00 01- 2014年6月11：02结束

101 100010 01-June-2014 11:04 01-June-2014 11:06结束

101 100010 01-June-2014 11 ：08 01-June-2014 11:08 start

I have table in this format

code Bscid st_time status
101 100010 01-June-2014 11:00 start
101 100010 01-June-2014 11:02 end
101 100010 01-June-2014 11:04 start
101 100010 01-June-2014 11:06 end
101 100010 01-June-2014 11:08 start


Out Put format


code Bscid st_time end_time status
101 100010 01-June-2014 11:00 01-June-2014 11:02 end
101 100010 01-June-2014 11:04 01-June-2014 11:06 end
101 100010 01-June-2014 11:08 01-June-2014 11:08 start

推荐答案

SELECT 
	Code,Bscid, max(st_time) st_time,isnull(max(edtime),max(st_time)) as ed_time,min([status]) [status]
FROM
(
	SELECT row_number() OVER(ORDER by Code,Bscid, st_time) as srno, Code,Bscid, st_time,NULL as edtime,[status] FROM A
	WHERE [status]='start'
	
	UNION 
	
	SELECT row_number() OVER(ORDER by Code,Bscid, st_time) as srno,Code,Bscid, NULL st_time,st_time as edtime,[status] FROM A
	WHERE [status]='end'
)
as temp
group by srno,Code,Bscid

快乐编码！

：）

Happy Coding!
:)

这篇关于sql查询表中的反向数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！