1.SQL2000_SQL2005_2008语句大全worSQLd完整版[1]

SQL code

--设置 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 code

/*--功能说明 下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库 该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件 ALTER DATABASE语句将用户定义文件组指定为默认文件组。 之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。 最后，将文件组中的指定数据文件删除 --*/ --切换到 master 数据库USE masterGO --A. 创建数据库 MyDBCREATE DATABASE MyDBON 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'),--次要数据文件2FILEGROUP MyDB_FG2--用户定义文件组2 ( NAME= 'MyDB_FG1_Dat', FILENAME= 'e:\\MyDB_FG2.ndf')--次要数据文件LOG ON --日志文件 ( NAME='MyDB_log', FILENAME='d:\\MyDB.ldf')GO --B. 修改默认数据文件组ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1DEFAULTGO --切换到新建的数据库 MyDBUSE MyDB--C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2CREATE TABLE MyTable ( colaint PRIMARY KEY , colbchar(8) , colcimage ) TEXTIMAGE_ON MyDB_FG2--在用户定义文件组MyDB_FG2上创建索引CREATE INDEX IX_MyTableON MyTable(cola)ON MyDB_FG2GO --D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)--删除数据文件MyDB_FG1_Dat1ALTER DATABASE MyDB REMOVEFILE MyDB_FG1_Dat1

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

SQL code

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

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

SQL code

--A. 测试 datetime 精度问题DECLARE @t TABLE(datechar(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(dateas 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:

SQL code

--字符转换为日期时,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 datetimeSET @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 /*== 这是很多人经常犯的错误,对非日期型转换使用日期的样式 ==*/SELECT CONVERT(varchar,'2003-1-11',101)--结果:2003-1-11

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

SQL code

--1./*--说明 SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响 但不影响明确指定了的CONVERT处理。 --*/ --示例 ，在下面的示例中，第一个CONVERT转换未指定，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了，转换结果受的影响。--设置输入日期顺序为 日/月/年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 MDYSET @dt='010203'SELECT @dt--结果: 2001-02-03 00:00:00.000--设置SET DATEFORMAT为:日月年SET DATEFORMAT DMYSET @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

SQL code

--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。USE master--设置会话的语言环境为: EnglishSET 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 日期推算处理.sql

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

SQL code

--字符转换为日期时,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 datetimeSET @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 /*== 这是很多人经常犯的错误,对非日期型转换使用日期的样式 ==*/SELECT CONVERT(varchar,'2003-1-11',101)--结果:2003-1-11

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

SQL code

--1./*--说明 SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响 但不影响明确指定了的CONVERT处理。 --*/ --示例 ，在下面的示例中，第一个CONVERT转换未指定，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了，转换结果受的影响。--设置输入日期顺序为 日/月/年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 MDYSET @dt='010203'SELECT @dt--结果: 2001-02-03 00:00:00.000--设置SET DATEFORMAT为:日月年SET DATEFORMAT DMYSET @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

SQL code

--以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。USE master--设置会话的语言环境为: EnglishSET 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

SQL code

DECLARE @dt datetimeSET @dt=GETDATE()--1．短日期格式：yyyy-m-dSELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')--2．长日期格式：yyyy年mm月dd日--A. 方法1SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'--B. 方法2SELECT 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:mmmSELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)

日期推算处理.sql

SQL code

DECLARE @dt datetimeSET @dt=GETDATE()DECLARE @number intSET @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

SQL code

ＱＱ291911320

--测试数据DECLARE @t TABLE(IDint,Namevarchar(10),Birthdaydatetime)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','19-12-11' DECLARE @dt1 datetime,@dt2 datetime --查询 2003-12-05 至 2004-02-28 生日的记录SELECT @dt1='2003-12-05',@dt2='2004-02-28'SELECT * FROM @tWHERE 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 19-12-11 00:00:00.000 --*/ --查询 2003-12-05 至 2006-02-28 生日的记录SET @dt2='2006-02-28'SELECT * FROM @tWHERE 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 19-12-11 00:00:00.000 --*/

生成日期列表的函数.sql

日期推算处理.sql

SQL code

DECLARE @dt datetimeSET @dt=GETDATE()DECLARE @number intSET @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

SQL code

--测试数据DECLARE @t TABLE(IDint,Namevarchar(10),Birthdaydatetime)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','19-12-11' DECLARE @dt1 datetime,@dt2 datetime --查询 2003-12-05 至 2004-02-28 生日的记录SELECT @dt1='2003-12-05',@dt2='2004-02-28'SELECT * FROM @tWHERE 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 19-12-11 00:00:00.000 --*/ --查询 2003-12-05 至 2006-02-28 生日的记录SET @dt2='2006-02-28'SELECT * FROM @tWHERE 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 19-12-11 00:00:00.000 --*/

生成日期列表的函数.sql

复杂年月处理.sql

SQL code

--定义基本数字表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 #tbyfrom (select idfrom syscolumns) a, (select idfrom syscolumns) b, (select idfrom syscolumns) c--为月表数据处理准备临时表select top 12 m=identity(int,1,1)into #tbmfrom syscolumns/*--数据处理--*/--年表数据处理select a.*from(select a.代码,a.名称,年份=b.yfrom @T1 a,#tby bwhere b.ybetween year(参加时间)and year(终止时间) ) aleft join @NB bon a.代码=b.代码and a.年份=b.年份where b.代码is null --月表数据处理select a.*from(select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.mas varchar),2)from @T1 a,#tby b,#tbm cwhere b.y*100+c.mbetween convert(varchar(6),参加时间,112)and convert(varchar(6),终止时间,112) ) aleft join @YB bon a.代码=b.代码and a.年份=b.年份and a.月份=b.月份where b.代码is nullorder by a.代码,a.名称,a.年份,a.月份--删除数据处理临时表drop table #tby,#tbm

交叉表.sql

SQL code

--示例--示例数据create table tb(IDint,Timedatetime)insert tbselect 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.Timeend,[Mon]=sum(case a.weekwhen 1 then 1 else 0 end),[Tue]=sum(case a.weekwhen 2 then 1 else 0 end),[Wed]=sum(case a.weekwhen 3 then 1 else 0 end),[Thu]=sum(case a.weekwhen 4 then 1 else 0 end),[Fri]=sum(case a.weekwhen 5 then 1 else 0 end),[Sat]=sum(case a.weekwhen 6 then 1 else 0 end),[Sun]=sum(case a.weekwhen 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 )aright 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)')bon a.Time>=b.aand a.Time任意两个时间之间的星期几的次数-横.sql

ＱＱ291911320

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

SQL code

--交叉表,根据优先级取数据,日期处理create table tb(qidint,ridnvarchar(4),tagnamenvarchar(10),starttimesmalldatetime,endtimesmalldatetime,startweekdayint,endweekdayint,startdatesmalldatetime,enddatesmalldatetime,dint)insert tbselect 1,'A1','未订','08:00','09:00',1 ,5 ,null ,null ,1union all select 1,'A1','未订','09:00','10:00',1 ,5 ,null ,null ,1union all select 1,'A1','未订','10:00','11:00',1 ,5 ,null ,null ,1union 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',2union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2union all select 1,'A2','未订','08:00','09:00',1 ,5 ,null ,null ,1union all select 1,'A2','未订','09:00','10:00',1 ,5 ,null ,null ,1union 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',2union 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',2go /*--楼主这个问题要考虑几个方面 1. 取星期时,set datefirst 的影响 2. 优先级问题 3. qid,rid 应该是未知的(动态变化的) --*/ --实现的存储过程如下create proc p_qry@date smalldatetime --要查询的日期asset nocountondeclare @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 #tfrom(select top 100 percent qid,rid,tagname, starttime=convert(char(5),starttime,108), endtime=convert(char(5),endtime,108)from tbwhere (@week between startweekdayand endweekday)or(@date between startdateand enddate)order by qid,rid,starttime,ddesc)aselect @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 #tgroup by qid,ridexec('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各种字符串合并处理示例.sql

SQL code

--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1varchar(10),col2int)INSERT tbSELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3 --合并处理--定义结果集表变量DECLARE @t TABLE(col1varchar(10),col2varchar(100))--定义游标并进行合并处理DECLARE tbCURSOR LOCALFORSELECT col1,col2FROM tbORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tbINTO @col1,@col2SELECT @col1_old=@col1,@s=''WHILE @@FETCH_STATUS=0BEGIN 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 tbINTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/GO /*==============================================*/ --3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1varchar(10),col2int)INSERT tbSELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO --合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN DECLARE @re varchar(100)SET @re='' SELECT @re=@re+','+CAST(col2as varchar)FROM tbWHERE col1=@col1 RETURN(STUFF(@re,1,1,''))ENDGO --调用函数SELECT col1,col2=dbo.f_str(col1)FROM tbGROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果 col1 col2 ---------- ----------- a 1,2 b 1,2,3 --*/GO

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

--3.3.3 使用临时表实现字符串合并处理的示例

--处理的数据

CREATE TABLE tb(col1varchar(10),col2int)

INSERT tbSELECT '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(col2as varchar(100))

INTO #tFROM tb

ORDER BY col1,col2

DECLARE @col1 varchar(10),@col2 varchar(100)

UPDATE #tSET 

   @col2=CASE WHEN @col1=col1THEN @col2+','+col2ELSE col2END,

   @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 #tGROUP BY col1

/*--结果

col1       col2

---------- -----------

a          1,2

b          1,2,3

--*/

--删除测试

DROP TABLE tb,#t

GO

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

--3.3.4.1 每组 <=2 条记录的合并

--处理的数据

CREATE TABLE tb(col1varchar(10),col2int)

INSERT tbSELECT '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(col1varchar(10),col2int)

INSERT tbSELECT '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 col2FROM tbWHERE col1=a.col1AND col2NOT 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