在论坛中出现的比较难的sql问题：12(递归问题2)

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

1、请教一个MSSQLSELECT语名的写法 

http://bbs.csdn.net/topics/390649166?page=1#post-396149924

表结构
id  ids
1   3,8,83,92,215,7

传入一个值8,92要取出3,83,215,7
即是取出不存在于传入ID串其它所有ID都取出来.
请问这个SELECT 语语应该怎么写。

递归replace，我的解法：

drop table t go  create table t(id int,  ids varchar(100))  insert into t select 1 ,  '3,8,83,92,215,7' go    declare @a varchar(100) = '8,7'  ;with tt as ( select id, ids,@a+',' as a,ids+',' as ids_t from t where ids like ('%' + REPLACE(@a,',', '%') + '%') ),  ttt as ( select id,ids,        cast(a as varchar(max)) as a,        cast(ids_t as varchar(max)) as ids_t ,        1  as level from tt  union all  select id,ids,        cast(stuff(a,1,charindex(',',a),'') as varchar(max)) ,        cast(replace(ids_t,left(a,charindex(',',a)),'') as varchar(max)),        level + 1 from ttt where charindex(',',a) > 0  )   select id, ids_t from  ( select id, ids,left(ids_t,len(ids_t)-1) as ids_t,        ROW_NUMBER() over(partition by id order by level desc) as rownum  from ttt  )a where rownum = 1 /* id	ids_t 1	3,83,92,215 */ 

2、时间间隔计算.

http://bbs.csdn.net/topics/390608930 

这个问题非常复杂。

start_time                  end_time
2013-09-11 17:26:02.382      2013-09-24 10:38:01.41
2013-09-18 17:02:40.444      2013-09-22 15:27:58.984
2013-09-18 08:21:32.036      2013-09-22 15:31:52.499
2013-09-13 16:28:29.832      2013-09-16 09:41:47.988
2013-09-09 10:59:59.835      2013-09-10 14:06:21.223

要求计算这两个列的时间差 但是要去除9月份的正常休假并且只计算正常工作时间（上午8:30--12:00 下午14:00--18:00）

计算结果如下：

start_time                  end_time                        diff_time（小时）
2013-09-11 17:26:02.382      2013-09-24 10:38:01.41         55.1
2013-09-18 17:02:40.444      2013-09-22 15:27:58.984        5.9
2013-09-18 08:21:32.036      2013-09-22 15:31:52.499        12.5
2013-09-13 16:28:29.832      2013-09-16 09:41:47.988        2.7
2013-09-09 10:59:59.835      2013-09-10 14:06:21.223        1.1

请各位大大帮忙看看这个时间差应该怎么计算  谢谢了

我的解法： 

if object_id('tab') is not null  drop table tab  if object_id('holiday') is not null drop table holiday  go create table tab(start_time datetime,end_time datetime)  insert into tab select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'   create table holiday(h_date datetime)  insert into holiday select '2013-09-01' union  select '2013-09-07'  union  select '2013-09-08' union  select '2013-09-14'union  select '2013-09-15'union  select '2013-09-19'union  select '2013-09-20'union  select '2013-09-21'union  select '2013-09-29' go    WITH calendar    --产生日历 AS ( SELECT CAST('2013-09-01' as varchar(10)) AS r  --月份的开始日期  UNION ALL  SELECT convert(VARCHAR(10),dateadd(day,1,r),120) FROM calendar WHERE r < '2013-09-30'    --月份的结束日期 ),  tt   --计算时间间隔，单位为秒 as ( SELECT t.start_time,        t.end_time,                c.r,        h.h_date,                /*        通过tab表和calendar表的关联，就能把开始时间到结束时间，多对应的多天，        都给关联出来，        比如开始时间 2013-09-18 08:21:32.037	结束时间 2013-09-22 15:31:52.500，        其实就是，18、19、20、21、22这一共5天，会由原来的1条记录，现在变为5条记录。                如果h_date为null，说明这一天不是假日,        就需要计算时间间隔，有几种可能性：        1.开始时间和结束时间，在同一天的        2.当前日期和开始日期相同        3.当前日期和结束日期相同        4.当前日期是在开始日期和结束日期之间的某天                如果h_date是null，那么返回0，说明是节假日，就不用计算时间间隔了         */        case when h_date IS null and                  convert(varchar(10),t.start_time_temp,120) = c.r and                  CONVERT(varchar(10),t.end_time_temp,120) = c.r                  then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'                                 and not (convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')                                 then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00')                            else 0                       end +                       case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'                                 and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')                                 then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)                            else 0                       end +                       case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'                                  and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')                                 or                                 (convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'                                  and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')                                 then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)                            else 0                       end                               /*             注意下面的计算逻辑是，如果这天不是假日，同时与开始日期相同             那么就要计算时间间隔，如果时间是在上午的工作时间范围内，             那么用当前日期的12点，减去开始日期，就是时间间隔，但还必须要加上下午的工作时间，             也就是4个小时，转化为秒数，就是4*3600             */                                 when h_date IS null and                  convert(varchar(10),t.start_time_temp,120) = c.r                   then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'                                 then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600                            else 0                       end +                       case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'                                 then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')                            else 0                       end                                                     when h_date IS null and                  CONVERT(varchar(10),t.end_time_temp,120) = c.r                  then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'                                 then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp)                             else 0                       end +                       case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'                                 then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600                            else 0                       end                          when h_date is null and                  convert(varchar(10),t.start_time_temp,120) < c.r and                  CONVERT(varchar(10),t.end_time_temp,120) > c.r                       then  7.5 * 3600                               when h_date IS null                  then 0        end   as seconds  FROM  ( /* 这里之所以要转换，是由于有些时间比如 start_time为2013-09-18 08:21:32.037, 不在正常工作时间（上午8:30--12:00 下午14:00--18:00）内， 所以要先转化为正常工作时间，否则后面的case when的逻辑判断就太复杂了。 */ SELECT start_time,        end_time,                case when CONVERT(varchar(5),start_time,114) < '08:30'                  then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)                               when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'                  then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)                          else start_time        end as start_time_temp,         case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'                  then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)                          when CONVERT(varchar(5),end_time,114) > '18:00'                  then cast(CONVERT(varchar(10),end_time,120) + ' 18:00:00' AS datetime)                          else end_time        end as end_time_temp   FROM tab ) t inner join calendar c         on convert(varchar(10),t.start_time,120) <= c.r            and convert(varchar(10),t.end_time,120) >= c.r  left join holiday h        on c.r = h.h_date  --OPTION(MAXRECURSION 1000)  --限制最大递归次数 )  --select * from tt  select start_time,        end_time,                --汇总秒数，同时转化为小时        cast(round(SUM(seconds) / 3600 ,1,1) as numeric(10,1)) as diff_time from tt group by start_time,          end_time           /* start_time				end_time				diff_time 2013-09-09 10:59:59.837	2013-09-09 14:06:21.223	1.1 2013-09-13 16:28:29.833	2013-09-16 09:41:47.987	2.7 2013-09-18 17:02:40.443	2013-09-22 15:27:58.983	5.9 2013-09-18 08:21:32.037	2013-09-22 15:31:52.500	12.5 2013-09-11 17:26:02.383	2013-09-24 10:38:01.410	55.1 */