死人了，帮帮俺吧，千分相赠！帮忙写一个sql语句或存储过程～～～

Question

帮忙写一个sql语句或存储过程！！！！
表：liucheng（流程名称，字符）
    riqi（日期，日期）
    qssj（起始时间，日期时间）
    zzsj（中止时间，日期时间）
要求：
    运行峰时时间段  7：30－11：30   15：00－22：00
    运行平时时间段  11：30－17：00  5：00－7：30
    运行谷时时间段  22：00－5：00
    求出各个流程在选择的日期中峰平谷的时间分别求和。
结果：
    liucheng,fssj(峰时时间和)，pssj,gssj
如： 23流程   202               55    23

Answer 1

declare @liucheng_name varchar(40)  --声明一个流程名称变量declare liucheng_cursor cursor for
select distinct [liucheng] from TABLE_NAME --声明一个指向流程名称集合的游标OPEN liucheng_cursor  --打开流程名称集合
FETCH NEXT FROM liucheng_cursor INTO @liucheng_name  --从集合中取一个值，保存在流程名称变量中
WHILE @@FETCH_STATUS = 0  --遍历流程名称集合
begindeclare @fssj int  
set @fssj=(select count(*) from TABLE_NAME where [liucheng]=@liucheng_name and (起始时间与中止时间处在峰时时间段条件判断语句……))
declare @pssj int
set @pssj=(select count(*) from TABLE_NAME where [liucheng]=@liucheng_name and (起始时间与中止时间处在平时时间段条件判断语句……)) 
…………end
close liucheng_cursor  --关闭游标
DEALLOCATE liucheng_cursor --删除游标@liucheng_name中存储的是流程名称，@fssj中存储的是峰时时间流程的数量…………

Answer 2

Sorry 还漏了一句,在end之前还需加上下面这句
FETCH NEXT FROM liucheng_cursor INTO @liucheng_name  --取下一个流程名称
否则会是一个始终统计第一个流程的死循环。

Answer 3

谢谢楼上，看看我写的：
select liucheng,sum(
          case when qssj<=convert(nvarchar(11),qssj,121)+'07:30:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'07:30:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'11:30:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'07:30:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'07:30:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'11:30:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'07:30:00',convert(nvarchar(11),qssj,121)+'11:30:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'07:30:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'11:30:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'11:30:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'07:30:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'11:30:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'11:30:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'11:30:00')
               else '0' end +          case when qssj<=convert(nvarchar(11),qssj,121)+'15:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'15:00:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'22:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'15:00:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'15:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'22:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'15:00:00',convert(nvarchar(11),qssj,121)+'22:00:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'15:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'22:00:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'22:00:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'15:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'22:00:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'22:00:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'22:00:00')
               else '0' end )/60.00 as 'fssj' 
-----------------平时时间----------------------------------------------------
    ,sum( case when qssj<=convert(nvarchar(11),qssj,121)+'11:30:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'11:30:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'17:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'11:30:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'11:30:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'17:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'11:30:00',convert(nvarchar(11),qssj,121)+'17:00:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'11:30:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'17:00:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'17:00:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'11:30:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'17:00:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'17:00:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'17:00:00')
               else '0' end +          case when qssj<=convert(nvarchar(11),qssj,121)+'05:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'05:00:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'07:30:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'05:00:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'05:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'07:30:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'05:00:00',convert(nvarchar(11),qssj,121)+'07:30:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'05:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'07:30:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'07:30:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'05:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'07:30:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'07:30:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'07:30:00')
               else '0' end )/60.00 as 'pssj' 
-------------------谷时时间-----------------------------------------------------
    ,sum( case when qssj<=convert(nvarchar(11),qssj,121)+'22:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'22:00:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'23:59:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'22:00:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'22:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'23:59:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'22:00:00',convert(nvarchar(11),qssj,121)+'23:59:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'22:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'23:59:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'23:59:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'22:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'23:59:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'23:59:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'23:59:00')
               else '0' end +          case when qssj<=convert(nvarchar(11),qssj,121)+'00:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'00:00:00' 
                 and zzsj<=convert(nvarchar(11),qssj,121)+'05:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'00:00:00',zzsj) 
               when qssj<=convert(nvarchar(11),qssj,121)+'00:00:00' 
                 and zzsj>=convert(nvarchar(11),qssj,121)+'05:00:00' 
               then datediff(minute,convert(nvarchar(11),qssj,121)+'00:00:00',convert(nvarchar(11),qssj,121)+'05:00:00')                 
               when qssj>=convert(nvarchar(11),qssj,121)+'00:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'05:00:00'
                 and zzsj<=convert(nvarchar(11),qssj,121)+'05:00:00' 
               then datediff(minute,qssj,zzsj) 
               when qssj>=convert(nvarchar(11),qssj,121)+'00:00:00' 
                 and qssj<=convert(nvarchar(11),qssj,121)+'05:00:00'
                 and zzsj>=convert(nvarchar(11),qssj,121)+'05:00:00' 
               then datediff(minute,qssj,convert(nvarchar(11),qssj,121)+'05:00:00')
               else '0' end )/60.00 as 'gssj' from (select distinct * from ddrb2 where convert(varchar(10),qssj,121)>='2002-01-01' and convert(varchar(10),qssj,121) <='2003-6-1' and leibie='运行中') as t1  group by liucheng