SQL大全2

复杂年月处理.sql

--定义基本数字表
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

--示例

--示例数据
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

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>=4 then 1 else 0 end
            end
        ,周五=case a
            when -1 then case when 5 between b and c then 1 else 0 end
            when  0 then case when b<=5 then 1 else 0 end
                    +case when c>=5 then 1 else 0 end
            else a+case when b<=5 then 1 else 0 end
                +case when c>=5 then 1 else 0 end
            end
        ,周六=case a
            when -1 then case when 6 between b and c then 1 else 0 end
            when  0 then case when b<=6 then 1 else 0 end
                    +case when c>=6 then 1 else 0 end
            else a+case when b<=6 then 1 else 0 end
                +case when c>=6 then 1 else 0 end
            end
        ,周日=case a
            when -1 then case when 0 between b and c then 1 else 0 end
            when  0 then case when b<=0 then 1 else 0 end
                    +case when c>=0 then 1 else 0 end
            else a+case when b<=0 then 1 else 0 end
                +case when c>=0 then 1 else 0 end
            end
    from(
        select 跨周数=case when @dt_begin<@dt_end
                then (datediff(day,@dt_begin,@dt_end)+7)/7
                else (datediff(day,@dt_end,@dt_begin)+7)/7 end
            ,a=case when @dt_begin<@dt_end
                then datediff(week,@dt_begin,@dt_end)-1
                else datediff(week,@dt_end,@dt_begin)-1 end
            ,b=case when @dt_begin<@dt_end
                then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
                else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
            ,c=case when @dt_begin<@dt_end
                then (@@datefirst+datepart(weekday,@dt_end)-1)%7
                else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
)
go

 统计--交叉表+日期+优先.sql

--交叉表,根据优先级取数据,日期处理

create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)
insert tb select 1,'A1','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 
--union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,'A2','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,'A2','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
--union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
go

/**//*--楼主这个问题要考虑几个方面

    1. 取星期时,set datefirst 的影响
    2. 优先级问题
    3. qid,rid 应该是未知的(动态变化的)
--*/

--实现的存储过程如下
create proc p_qry
@date smalldatetime --要查询的日期
as
set nocount on
declare @week int,@s nvarchar(4000)
--格式化日期和得到星期
select @date=convert(char(10),@date,120)
    ,@week=(@@datefirst+datepart(weekday,@date)-1)%7
    ,@s=''
select id=identity(int),* into #t
from(
    select top 100 percent
        qid,rid,tagname,
        starttime=convert(char(5),starttime,108),
        endtime=convert(char(5),endtime,108)
    from tb
    where (@week between startweekday and endweekday)
        or(@date between startdate and enddate)
    order by qid,rid,starttime,d desc)a

select @s=@s+N',['+rtrim(rid)
    +N']=max(case when qid='+rtrim(qid)
    +N' and rid=N'''+rtrim(rid)
    +N''' then tagname else N'''' end)'
from #t group by qid,rid
exec('
select starttime,endtime'+@s+' 
from #t a
where not exists(
    select * from #t
    where qid=a.qid and rid=a.rid 
        and starttime=a.starttime
        and endtime=a.endtime
        and id<a.id)
group by starttime,endtime')
go

--调用
exec p_qry '2005-1-17'
exec p_qry '2005-1-18'
go

--删除测试
drop table tb
drop proc p_qry

/**//*--测试结果

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   未订         未订
09:00     10:00   未订         未订
10:00     11:00   未订         未订

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   装修         未订
09:00     10:00   未订         装修
10:00     11:00   装修         未订
--*/

 各种字符串分拆处理函数.sql

--各种字符串分函数

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    DECLARE @splitlen int
    SET @splitlen=LEN(@split+'a')-2
    WHILE CHARINDEX(@split,@s)>0
    BEGIN
        INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    END
    INSERT @re VALUES(@s)
    RETURN
END
GO


/**//*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE(ID int IDENTITY,b bit)
    INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

    INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
    FROM @t
    WHERE ID<=LEN(@s+'a') 
        AND CHARINDEX(@split,@split+@s,ID)=ID
    RETURN
END
GO

/**//*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO

--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO

--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
    SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
    FROM tb_splitSTR
    WHERE ID<=LEN(@s+'a') 
        AND CHARINDEX(@split,@split+@s,ID)=ID)
GO


/**//*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),    --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
    --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE(ID int IDENTITY,b bit)
    INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

    INSERT @re 
    SELECT    No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
        Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
    FROM(
        SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
        FROM @t
        WHERE ID<=LEN(@s+'a') 
            AND CHARINDEX(@split,@split+@s,ID)=ID)a
    RETURN
END
GO


/**//*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
    DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
    INSERT @splits(split)
    SELECT 'AC' UNION ALL
    SELECT 'BC' UNION ALL
    SELECT 'CC' UNION ALL
    SELECT 'DC'    
    DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
    SELECT TOP 1 
        @pos1=1,@split=split,@splitlen=splitlen
    FROM @splits
    WHERE @s LIKE split+'%'
    WHILE @pos1>0
    BEGIN
        SELECT TOP 1
            @pos2=CHARINDEX(split,@s,@splitlen+1)
        FROM @splits
        WHERE CHARINDEX(split,@s,@splitlen+1)>0
        ORDER BY CHARINDEX(split,@s,@splitlen+1)
        IF @@ROWCOUNT=0
        BEGIN
            INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
            RETURN
        END
        ELSE
        BEGIN
            INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
            SELECT TOP 1 
                @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
            FROM @splits
            WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
        END
    END
    RETURN
END
GO

 各种字符串合并处理示例.sql

--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
    IF @col1=@col1_old
        SELECT @s=@s+','+CAST(@col2 as varchar)
    ELSE
    BEGIN
        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
    END
    FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/**//*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO


/**//*==============================================*/


--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+','+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/**//*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO

/**//*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT * FROM #t
/**//*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/**//*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO


/**//*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(col2) as varchar)
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/**//*--结果
col1       col2      
---------- ----------
a          1,2
b          1,2
c          3
--*/

--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=3 THEN ','
                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
            ELSE ''
        END
        +CASE 
            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
            ELSE ''
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/**//*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO