时间分段显示处理

原贴:http://topic.youkuaiyun.com/u/20100329/11/43a17b71-f9e4-44b9-8de1-a200911508f4.html?61069

 

select * from table#
表的全部数据如下：
2009-09-03 15:20:00.000 a
2009-09-03 15:30:00.000 b
2009-09-03 15:45:00.000 c
2009-09-03 16:10:00.000 b

有两个值可选择：30/60

如果我选择间隔时间为60分钟，出来的数据如下：
15:00 -- 16:00 a/b/c
16:00 -- 17:00 b

如果我选择间隔时间为30分钟，出来的数据如下：
15:00 -- 15:30 a/b
15:30 -- 15:59 c
16:00 -- 16:30 b

 

 

 

 

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-03-29 18:20:28

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([a] DATETIME,[b] NVARCHAR(10))

INSERT [tb]

SELECT N'2009-09-03 15:20:00.000','a' UNION ALL

SELECT N'2009-09-03 15:30:00.000','b' UNION ALL

SELECT N'2009-09-03 15:45:00.000','c' UNION ALL

SELECT N'2009-09-03 16:10:00.000','b'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

if OBJECT_ID('sp_test') is not null

    drop proc sp_test

go

create proc sp_test

@i int=30

as

if @i not in(30,60) return 0

else

begin

    if @i=30

    with t as

    (

       select case   when DATEDIFF(mi,DATEADD(hh,datediff(hh,0,a),0),a) between 0 and 30

                  then CONVERT(varchar(5),DATEADD(hh,datediff(hh,0,a),0),8) +'~'

                     +CONVERT(varchar(5),DATEADD(mi,30,DATEADD(hh,datediff(hh,0,a),0)),8)

                  when DATEDIFF(mi,DATEADD(hh,datediff(hh,0,a),0),a) between 31 and 60

                  then CONVERT(varchar(5),DATEADD(mi,30,DATEADD(hh,datediff(hh,0,a),0)),8) +'~'

                     +CONVERT(varchar(5),DATEADD(mi,59,DATEADD(hh,datediff(hh,0,a),0)),8)

              end as a,b

       from tb   

    )

    select a,STUFF((select '/'+b from t where a=a.a for xml path('')),1,1,'') b

    from t a

    group by a

    else

    with t as

    (

       select case   when DATEDIFF(mi,DATEADD(hh,datediff(hh,0,a),0),a) between 0 and 60

                  then CONVERT(varchar(5),DATEADD(hh,datediff(hh,0,a),0),8) +'~'

                     +CONVERT(varchar(5),DATEADD(hh,1,DATEADD(hh,datediff(hh,0,a),0)),8)

              end as a,b

       from tb

    )

    select a,STUFF((select '/'+b from t where a=a.a for xml path('')),1,1,'') b

    from t a

    group by a

end

GO

 

exec sp_test 30

/*

a              b

-----------   -----

15:00~15:30   a/b

15:30~15:59   c

16:00~16:30   b

*/

exec sp_test 60

/*

a              b

-----------   -----

15:00~16:00   a/b/c

16:00~17:00   b

*/