SQLServer 一个简单操作（续）

电玩女神 2022-08-27 03:50 185阅读 0赞

\--========================

\--设置内存选项

\--========================   
   
\--设置 min server memory 配置项   
EXEC sp\_configure N'min server memory (MB)',0    
\--设置 max server memory 配置项   
EXEC sp\_configure N'max server memory (MB)',256    
\--使更新生效   
RECONFIGURE WITH OVERRIDE   
   
\--============================================================   
   
\--========================

\--使用文件及文件组.sql

\--========================   
   
/\*--功能说明 下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库   
 该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件   
 ALTER DATABASE语句将用户定义文件组指定为默认文件组。    
 之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。   
 最后，将文件组中的指定数据文件删除 --\*/    
 \--切换到 master 数据库   
 USE master   
 GO    
 \--A. 创建数据库 MyDB   
 CREATE DATABASE MyDB   
    ON PRIMARY      \--主文件组和主要数据文件    
    (    NAME='MyDB\_Primary',    
        FILENAME= 'c:/MyDB\_Prm.mdf'   
    ),   
    FILEGROUP MyDB\_FG1  \--用户定义文件组1    
    (   
         NAME= 'MyDB\_FG1\_Dat1',    
         FILENAME= 'c:/MyDB\_FG1\_1.ndf'   
    ),   
    \--次要数据文件1    
    (    
        NAME= 'MyDB\_FG1\_Dat2',    
        FILENAME= 'd:/MyDB\_FG1\_2.ndf'   
    ),   
    \--次要数据文件2   
    FILEGROUP MyDB\_FG2\--用户定义文件组2    
    (    
        NAME= 'MyDB\_FG1\_Dat',    
        FILENAME= 'e:/MyDB\_FG2.ndf'   
    ),   
    \--次要数据文件LOG ON --日志文件    
    (    
        NAME='MyDB\_log',    
        FILENAME='d:/MyDB.ldf'   
    )    
   
\--DUMP TRANSACTION MyDB WITH NO\_LOG and truncate\_only   
GO    
   
\--B. 修改默认数据文件组   
ALTER DATABASE MyDB    
    MODIFY FILEGROUP MyDB\_FG1 DEFAULT   
GO   
\---切换到新建的数据库 MyDB   
USE MyDB   
   
\--C. 在默认文件组MyDB\_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB\_FG2   
CREATE TABLE MyTable    
    (    
        cola int PRIMARY KEY ,    
        colb char(8) ,    
        colc image    
    )    
TEXT IMAGE\_ON MyDB\_FG2   
\--在用户定义文件组MyDB\_FG2上创建索引   
CREATE INDEX IX\_MyTable   
    ON MyTable(cola)   
    ON MyDB\_FG2   
GO    
       
\--D. 将要删除数据文件MyDB\_FG1\_Dat1上的数据转移到其他数据文件中，   
\--   并且清空数据文件MyDB\_FG1\_Dat1   
DBCC SHRINKFILE(MyDB\_FG1\_Dat1,EMPTYFILE)   
\--删除数据文件MyDB\_FG1\_Dat1   
ALTER DATABASE MyDB REMOVE FILE MyDB\_FG1\_Dat1   
   
\--==========================================================================   
   
\--===============================

\--调整tempdb数据库的文件属性.sql

\--===============================   
   
\--A. 将tempdb数据库的主数据文件大小设置为10MB。   
ALTER DATABASE tempdb    
    MODIFY FILE( name=tempdev, size=100 MB)   
GO    
\--B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。   
\--移动主数据文件   
ALTER DATABASE tempdb    
    MODIFY FILE ( NAME='tempdev', FILENAME='d:/tempdb.mdf')   
\--添加次要数据文件   
ALTER DATABASE tempdb   
    ADD FILE ( NAME='tempdata\_1', FILENAME='d:/tempdb\_data\_1.ndf')   
       
\--============================================================================   
   
\--===============================

\--日期概念理解中的一些测试.sql

\--===============================   
   
\--A. 测试 datetime 精度问题   
DECLARE @t TABLE(date char(21))   
INSERT @t SELECT '1900-1-1 00:00:00.000'   
INSERT @t SELECT '1900-1-1 00:00:00.001'   
INSERT @t SELECT '1900-1-1 00:00:00.009'   
INSERT @t SELECT '1900-1-1 00:00:00.002'   
INSERT @t SELECT '1900-1-1 00:00:00.003'   
INSERT @t SELECT '1900-1-1 00:00:00.004'   
INSERT @t SELECT '1900-1-1 00:00:00.005'   
INSERT @t SELECT '1900-1-1 00:00:00.006'   
INSERT @t SELECT '1900-1-1 00:00:00.007'   
INSERT @t SELECT '1900-1-1 00:00:00.008'   
SELECT date,转换后的日期=CAST(date as datetime)   
    FROM @t    
/\*--结果 date 转换后的日期    
\--------------------- --------------------------    
1900-1-1 00:00:00.000    
1900-01-01 00:00:00.000    
1900-1-1 00:00:00.001    
1900-01-01 00:00:00.000    
1900-1-1 00:00:00.009    
1900-01-01 00:00:00.010    
1900-1-1 00:00:00.002    
1900-01-01 00:00:00.003    
1900-1-1 00:00:00.003    
1900-01-01 00:00:00.003    
1900-1-1 00:00:00.004    
1900-01-01 00:00:00.003    
1900-1-1 00:00:00.005    
1900-01-01 00:00:00.007    
1900-1-1 00:00:00.006    
1900-01-01 00:00:00.007    
1900-1-1 00:00:00.007    
1900-01-01 00:00:00.007    
1900-1-1 00:00:00.008    
1900-01-01 00:00:00.007    
（所影响的行数为 10 行） --   
\*/   
GO    
\--B. 对于 datetime 类型的纯日期和时间的十六进制表示   
DECLARE @dt datetime    
\--单纯的日期   
    SET @dt='1900-1-2'SELECT CAST(@dt as binary(8))   
    \--结果: 0x0000000100000000   
    \--单纯的时间   
    SET @dt='00:00:01'SELECT CAST(@dt as binary(8))   
\--结果: 0x000000000000012CGO    
\--C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示   
DECLARE @dt smalldatetime    
\--单纯的日期   
    SET @dt='1900-1-2'SELECT CAST(@dt as binary(4))   
    \--结果: 0x00010000   
    \--单纯的时间   
    SET @dt='00:10'SELECT CAST(@dt as binary(4))   
\--结果: 0x0000000A   
   
\--======================================================================   
   
\--=================================

\--CONVERT在日期转换中的使用示例.sql

\--=================================   
   
\--字符转换为日期时,Style的使用   
\--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式   
SELECT CONVERT(datetime,'11/1/2003',101)   
\--结果:2003-11-01 00:00:00.000   
\--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式   
SELECT CONVERT(datetime,'11/1/2003',103)   
\--结果:2003-01-11 00:00:00.000    
/\*== 日期转换为字符串 ==\*/   
DECLARE @dt datetime   
    SET @dt='2003-1-11'    
\--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式   
SELECT CONVERT(varchar,@dt,101)   
\--结果:01/11/2003--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式   
SELECT CONVERT(varchar,@dt,103)   
\--结果:11/01/2003    
/\*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==\*/   
SELECT CONVERT(varchar,'2003-1-11',101)   
\--结果:2003-1-11   
   
   
\--=====================================================================   
   
\--=============================

\--SET DATEFORMAT对日期处理的影响.sql

\--=============================   
   
\--1.   
/\*--   
说明 SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响    
但不影响明确指定了style的CONVERT处理。    
\--\*/    
\--示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的   
\--影响，第二个CONVERT转换指定了style，转换结果受style的影响。   
\--设置输入日期顺序为 日/月/年   
SET DATEFORMAT DMY   
\--不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响   
SELECT CONVERT(datetime,'2-1-2005')   
\--结果: 2005-01-02 00:00:00.000   
\--指定Style参数的CONVERT转换不受SET DATEFORMAT的影响   
SELECT CONVERT(datetime,'2-1-2005',101)   
\--结果: 2005-02-01 00:00:00.000GO    
\--2.   
/\*--   
说明 如果输入的日期包含了世纪部分，则对日期进行解释处理时    
年份的解释不受SET DATEFORMAT设置的影响。    
\--\*/    
\--示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的   
\--世纪部分，解释的日期结果不同。   
DECLARE @dt datetime    
\--设置SET DATEFORMAT为:月日年   
SET DATEFORMAT MDY   
\--输入的日期中指定世纪部分   
SET @dt='01-2002-03'SELECT @dt   
\--结果: 2002-01-03 00:00:00.000   
\--输入的日期中不指定世纪部分   
SET @dt='01-02-03'SELECT @dt   
\--结果: 2003-01-02 00:00:00.000GO    
\--3.   
/\*--   
说明 如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时    
将忽略SET DATEFORMAT的设置。    
\--\*/    
\--示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，   
\--其解释的结果是一样的。   
DECLARE @dt datetime    
\--设置SET DATEFORMAT为:月日年   
SET DATEFORMAT MDY   
SET @dt='010203'SELECT @dt   
\--结果: 2001-02-03 00:00:00.000   
\--设置SET DATEFORMAT为:日月年   
SET DATEFORMAT DMY   
SET @dt='010203'SELECT @dt   
\--结果: 2001-02-03 00:00:00.000   
\--输入的日期中包含日期分隔符   
SET @dt='01-02-03'SELECT @dt   
\--结果: 2003-02-01 00:00:00.000   
   
\--==============================================================================   
   
\--===================================

\--SET LANGUAGE对日期处理的影响示例.sql

\--===================================   
   
\--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。   
USE master   
\--设置会话的语言环境为: English   
SET LANGUAGE N'English'   
SELECT DATENAME(Month,GETDATE())AS \[Month\],   
    DATENAME(Weekday,GETDATE())AS \[Weekday\],   
    CONVERT(varchar,GETDATE(),109)AS \[CONVERT\]   
/\*--   
结果: Month Weekday CONVERT    
\------------- -------------- -------------------------------    
March Tuesday Mar 15 2005 8:59PM   
 --\*/    
 \--设置会话的语言环境为: 简体中文   
 SET LANGUAGE N'简体中文'   
 SELECT DATENAME(Month,GETDATE())AS \[Month\],   
     DATENAME(Weekday,GETDATE())AS \[Weekday\],   
     CONVERT(varchar,GETDATE(),109)AS \[CONVERT\]   
/\*--结果 Month Weekday CONVERT    
 ------------- --------------- -----------------------------------------   
  05 星期四 05 19 2005 2:49:20:607PM    
\--\*/   
     
  \--=======================================================================   
     
  \--==================================

\--日期格式化处理.sql

\--==================================   
   
DECLARE @dt datetime   
SET @dt=GETDATE()   
\--1．短日期格式：yyyy-m-d   
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')   
\--2．长日期格式：yyyy年mm月dd日   
\--A. 方法1   
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'   
\--B. 方法2   
SELECT DATENAME(Year,@dt)+N'年'   
    +DATENAME(Month,@dt)+N'月'   
    +DATENAME(Day,@dt)+N'日'    
\--3．长日期格式：yyyy年m月d日   
SELECT DATENAME(Year,@dt)+N'年'   
    +CAST(DATEPART(Month,@dt)AS varchar)+N'月'   
    +DATENAME(Day,@dt)+N'日'    
\--4.完整日期\+时间格式：yyyy-mm-dd hh:mi:ss:mmm   
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)   
   
\--================================================================   
   
\--========================

\--日期推算处理.sql

\--========================   
   
DECLARE @dt datetime   
SET @dt=GETDATE()   
DECLARE @number int   
SET @number=3    
\--1．指定日期该年的第一天或最后一天   
\--A. 年的第一天   
SELECT CONVERT(char(5),@dt,120)+'1-1'    
\--B. 年的最后一天   
SELECT CONVERT(char(5),@dt,120)+'12-31'    
\--2．指定日期所在季度的第一天或最后一天   
\--A. 季度的第一天   
SELECT CONVERT(datetime,CONVERT(char(8),   
    DATEADD(Month,DATEPART(Quarter,@dt)\*3   
    -Month(@dt)-2,@dt),120)+'1')   
\--B. 季度的最后一天（CASE判断法）   
SELECT CONVERT(datetime,CONVERT(char(8),   
    DATEADD(Month,DATEPART(Quarter,@dt)\*3\-Month(@dt),@dt),120)   
    +CASE WHEN DATEPART(Quarter,@dt)in(1,4)THEN '31'ELSE '30' END)   
\--C. 季度的最后一天（直接推算法）   
SELECT DATEADD(Day,-1,CONVERT(char(8),   
    DATEADD(Month,1\+DATEPART(Quarter,@dt)\*3\-Month(@dt),@dt),120)+'1')   
\--3．指定日期所在月份的第一天或最后一天   
\--A. 月的第一天   
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')   
\--B. 月的最后一天   
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')   
\--C. 月的最后一天（容易使用的错误方法）   
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))   
\--4．指定日期所在周的任意一天   
SELECT DATEADD(Day,@number\-DATEPART(Weekday,@dt),@dt)   
\--5．指定日期所在周的任意星期几   
\--A. 星期天做为一周的第1天   
SELECT DATEADD(Day,@number\-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)   
\--B. 星期一做为一周的第1天   
SELECT DATEADD(Day,@number\-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7\-1,@dt)   
   
\--============================================================================   
   
\--================================

\--查询指定日期段内过生日的人员.sql

\--================================   
   
\--测试数据   
DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)   
INSERT @t    
    SELECT 1,'aa','1999-01-01'UNION ALL    
    SELECT 2,'bb','1996-02-29'UNION ALL    
    SELECT 3,'bb','1934-03-01'UNION ALL    
    SELECT 4,'bb','1966-04-01'UNION ALL    
    SELECT 5,'bb','1997-05-01'UNION ALL    
    SELECT 6,'bb','1922-11-21'UNION ALL    
    SELECT 7,'bb','1989-12-11'   
DECLARE @dt1 datetime,@dt2 datetime    
\--查询 2003-12-05 至 2004-02-28 生日的记录   
SELECT @dt1='2003-12-05',@dt2='2004-02-28'   
SELECT \* FROM @t   
    WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)   
    BETWEEN @dt1 AND @dt2 OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)   
    BETWEEN @dt1 AND @dt2   
/\*--结果 ID Name Birthday   
 ---------------- ---------------- --------------------------    
 1 aa 1999-01-01 00:00:00.000    
 7 bb 1989-12-11 00:00:00.000    
 --\*/    
 \--查询 2003-12-05 至 2006-02-28 生日的记录   
 SET @dt2='2006-02-28'   
 SELECT \* FROM @t   
    WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)   
    BETWEEN @dt1 AND @dt2 OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)   
    BETWEEN @dt1 AND @dt2   
/\*   
\--查询结果 ID Name Birthday    
\---------------- ----------------- --------------------------    
1 aa 1999-01-01 00:00:00.000    
2 bb 1996-02-29 00:00:00.000    
7 bb 1989-12-11 00:00:00.000    
\--\*/   
   
\--=========================================================================   
   
\--==========================

\--生成日期列表的函数.sql

\--==========================   
   
DECLARE @dt datetime   
SET @dt=GETDATE()   
DECLARE @number int   
SET @number=3    
\--1．指定日期该年的第一天或最后一天   
\--A. 年的第一天   
SELECT CONVERT(char(5),@dt,120)+'1-1'    
\--B. 年的最后一天   
SELECT CONVERT(char(5),@dt,120)+'12-31'    
\--2．指定日期所在季度的第一天或最后一天   
\--A. 季度的第一天   
SELECT CONVERT(datetime,   
    CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)\*3\-Month(@dt)-2,@dt),120)+'1')   
\--B. 季度的最后一天（CASE判断法）   
SELECT CONVERT(datetime,   
    CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)\*3\-Month(@dt),@dt),120)   
    +CASE WHEN DATEPART(Quarter,@dt)in(1,4)THEN '31'ELSE '30' END)   
\--C. 季度的最后一天（直接推算法）   
SELECT DATEADD(Day,-1,   
    CONVERT(char(8),DATEADD(Month,1\+DATEPART(Quarter,@dt)\*3\-Month(@dt),@dt),120)+'1')   
\--3．指定日期所在月份的第一天或最后一天   
\--A. 月的第一天   
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')   
\--B. 月的最后一天   
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')   
\--C. 月的最后一天（容易使用的错误方法）   
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))   
\--4．指定日期所在周的任意一天   
SELECT DATEADD(Day,@number\-DATEPART(Weekday,@dt),@dt)   
\--5．指定日期所在周的任意星期几   
\--A. 星期天做为一周的第1天   
SELECT DATEADD(Day,@number\-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)   
\--B. 星期一做为一周的第1天   
SELECT DATEADD(Day,@number\-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7\-1,@dt)   
   
\--=========================================================================   
   
\--===================

\--复杂年月处理.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    
\--</b.b    
删除测试   
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

\--=====================   
   
\--交叉表,根据优先级取数据,日期处理   
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   
\--</a.id)    
调用   
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

\--========================   
   
\--各种字符串分函数

\--3.3.1 使用游标法进行字符串合并处理的示例。

\--处理的数据   
CREATE TABLE tb(col1 varchar(10),col2 int)   
INSERT tb SELECT 'a',1UNION ALL    
    SELECT 'a',2UNION ALL    
    SELECT 'b',1UNION ALL    
    SELECT 'b',2UNION 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',1UNION ALL    
    SELECT 'a',2UNION ALL    
    SELECT 'b',1UNION ALL    
    SELECT 'b',2UNION 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   
\--ＱＱ291911320   
\--合并处理   
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