sql的大全代码（5）

复杂年月处理.sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 定义基本数字表 declare @T1 table (代码 int ,名称 varchar ( 10 ),参加时间 datetime ,终止时间 datetime ) insert into @T1 select 12 , ' 单位1 ' , ' 2003/04/01 ' , ' 2004/05/01 ' union all select 22 , ' 单位2 ' , ' 2001/02/01 ' , ' 2003/02/01 ' union all select 42 , ' 单位3 ' , ' 2000/04/01 ' , ' 2003/05/01 ' union all select 25 , ' 单位5 ' , ' 2003/04/01 ' , ' 2003/05/01 ' -- 定义年表 declare @NB table (代码 int ,名称 varchar ( 10 ),年份 int ) insert into @NB select 12 , ' 单位1 ' , 2003 union all select 12 , ' 单位1 ' , 2004 union all select 22 , ' 单位2 ' , 2001 union all select 22 , ' 单位2 ' , 2002 union all select 22 , ' 单位2 ' , 2003 -- 定义月表 declare @YB table (代码 int ,名称 varchar ( 10 ),年份 int ,月份 varchar ( 2 )) insert into @YB select 12 , ' 单位1 ' , 2003 , ' 04 ' union all select 22 , ' 单位2 ' , 2001 , ' 01 ' union all select 22 , ' 单位2 ' , 2001 , ' 12 ' -- 为年表+月表数据处理准备临时表 select top 8246 y = identity ( int , 1753 , 1 ) into #tby from ( select id from syscolumns) a, ( select id from syscolumns) b, ( select id from syscolumns) c -- 为月表数据处理准备临时表 select top 12 m = identity ( int , 1 , 1 ) into #tbm from syscolumns /* --数据处理-- */ -- 年表数据处理 select a. * from ( select a.代码,a.名称,年份 = b.y from @T1 a,#tby b where b.y between year (参加时间) and year (终止时间) ) a left join @NB b on a.代码 = b.代码 and a.年份 = b.年份 where b.代码 is null -- 月表数据处理 select a. * from ( select a.代码,a.名称,年份 = b.y,月份 =right ( ' 00 ' + cast (c.m as varchar ), 2 ) from @T1 a,#tby b,#tbm c where b.y * 100 + c.m between convert ( varchar ( 6 ),参加时间, 112 ) and convert ( varchar ( 6 ),终止时间, 112 ) ) a left join @YB b on a.代码 = b.代码 and a.年份 = b.年份 and a.月份 = b.月份 where b.代码 is null order by a.代码,a.名称,a.年份,a.月份 -- 删除数据处理临时表 drop table #tby,#tbm

交叉表.sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> -- 示例 -- 示例数据 create table tb(ID int ,Time datetime ) insert tb select 1 , ' 2005/01/24 16:20 ' union all select 2 , ' 2005/01/23 22:45 ' union all select 3 , ' 2005/01/23 0:30 ' union all select 4 , ' 2005/01/21 4:28 ' union all select 5 , ' 2005/01/20 13:22 ' union all select 6 , ' 2005/01/19 20:30 ' union all select 7 , ' 2005/01/19 18:23 ' union all select 8 , ' 2005/01/18 9:14 ' union all select 9 , ' 2005/01/18 18:04 ' go -- 查询处理： select case when grouping (b.Time) = 1 then ' Total ' else b.Time end , [ Mon ] = sum ( case a.week when 1 then 1 else 0 end ), [ Tue ] = sum ( case a.week when 2 then 1 else 0 end ), [ Wed ] = sum ( case a.week when 3 then 1 else 0 end ), [ Thu ] = sum ( case a.week when 4 then 1 else 0 end ), [ Fri ] = sum ( case a.week when 5 then 1 else 0 end ), [ Sat ] = sum ( case a.week when 6 then 1 else 0 end ), [ Sun ] = sum ( case a.week when 0 then 1 else 0 end ), [ Total ] = count (a.week) from ( select Time = convert ( char ( 5 ), dateadd (hour, - 1 ,Time), 108 ) -- 时间交界点是1am,所以减1小时，避免进行跨天处理 ,week = ( @@datefirst + datepart (weekday,Time) - 1 ) % 7 -- 考虑@@datefirst对datepart的影响 from tb )a right join ( select id = 1 ,a = ' 16:00 ' ,b = ' 19:59 ' ,Time = ' [5pm - 9pm) ' union all select id = 2 ,a = ' 20:00 ' ,b = ' 23:59 ' ,Time = ' [9pm - 1am) ' union all select id = 3 ,a = ' 00:00 ' ,b = ' 02:59 ' ,Time = ' [1am - 4am) ' union all select id = 4 ,a = ' 03:00 ' ,b = ' 07:29 ' ,Time = ' [4am - 8:30am) ' union all select id = 5 ,a = ' 07:30 ' ,b = ' 11:59 ' ,Time = ' [8:30am - 1pm) ' union all select id = 6 ,a = ' 12:00 ' ,b = ' 15:59 ' ,Time = ' [1pm - 5pm) ' )b on a.Time >= b.a and a.Time < b.b group by b.id,b.Time with rollup having grouping (b.Time) = 0 or grouping (b.id) = 1 go -- 删除测试 drop table tb /* --测试结果 Mon Tue Wed Thu Fri Sat Sun Total -------------- ----- ----- ----- ----- ----- ------ ---- ------- [5pm - 9pm) 0 1 2 0 0 0 0 3 [9pm - 1am) 0 0 0 0 0 0 2 2 [1am - 4am) 0 0 0 0 0 0 0 0 [4am - 8:30am) 0 0 0 0 1 0 0 1 [8:30am - 1pm) 0 1 0 0 0 0 0 1 [1pm - 5pm) 1 0 0 1 0 0 0 2 Total 1 2 2 1 1 0 2 9 （所影响的行数为 7 行） -- */

任意两个时间之间的星期几的次数-横.sql

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_weekdaycount] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' )) drop function [ dbo ] . [ f_weekdaycount ] GO /* --计算任意两个时间之间的星期几的次数(横向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 --邹建 2004.08(引用请保留此信息)-- */ /* --调用示例 select * from f_weekdaycount('2004-9-01','2004-9-02') -- */ create function f_weekdaycount( @dt_begin datetime , @dt_end datetime ) returns table as return ( select 跨周数 ,周一 = case a when - 1 then case when 1 between b and c then 1 else 0 end when 0 then case when b <= 1 then 1 else 0 end + case when c >= 1 then 1 else 0 end else a + case when b <= 1 then 1 else 0 end + case when c >= 1 then 1 else 0 end end ,周二 = case a when - 1 then case when 2 between b and c then 1 else 0 end when 0 then case when b <= 2 then 1 else 0 end + case when c >= 2 then 1 else 0 end else a + case when b <= 2 then 1 else 0 end + case when c >= 2 then 1 else 0 end end ,周三 = case a when - 1 then case when 3 between b and c then 1 else 0 end when 0 then case when b <= 3 then 1 else 0 end + case when c >= 3 then 1 else 0 end else a + case when b <= 3 then 1 else 0 end + case when c >= 3 then 1 else 0 end end ,周四 = case a when - 1 then case when 4 between b and c then 1 else 0 end when 0 then case when b <= 4 then 1 else 0 end + case when c >= 4 then 1 else 0 end else a + case when b <= 4 then 1 else 0 end + case when c >=