|
??
--========================?
--设置内存选项?
--========================?
?
--设置?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,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,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,?
?????DATENAME(Weekday,?
?????CONVERT(varchar,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),120),N'-0','-')?
--2．长日期格式：yyyy年mm月dd日?
--A.?方法1?
SELECT?STUFF(STUFF(CONVERT(char(8),112),5,0,N'年'),8,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),120)+CONVERT(char(12),114)?
?
--================================================================?
?
--========================?
--日期推算处理.sql?
--========================?
?
DECLARE?@dt?datetime?
SET?@dt=GETDATE()?
DECLARE?@number?int?
SET?@number=3??
--1．指定日期该年的第一天或最后一天?
--A.?年的第一天?
SELECT?CONVERT(char(5),120)+'1-1'??
--B.?年的最后一天?
SELECT?CONVERT(char(5),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,@dt)*3-Month(@dt),120)?
????+CASE?WHEN?DATEPART(Quarter,@dt)in(1,4)THEN?'31'ELSE?'30'?END)?
--C.?季度的最后一天（直接推算法）?
SELECT?DATEADD(Day,-1,1+DATEPART(Quarter,120)+'1')?
--3．指定日期所在月份的第一天或最后一天?
--A.?月的第一天?
SELECT?CONVERT(datetime,120)+'1')?
--B.?月的最后一天?
SELECT?DATEADD(Day,DATEADD(Month,1,120)+'1')?
--C.?月的最后一天（容易使用的错误方法）?
SELECT?DATEADD(Month,DATEADD(Day,-DAY(@dt),@dt))?
--4．指定日期所在周的任意一天?
SELECT?DATEADD(Day,@number-DATEPART(Weekday,@dt)?
--5．指定日期所在周的任意星期几?
--A.?星期天做为一周的第1天?
SELECT?DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)?
--B.?星期一做为一周的第1天?
SELECT?DATEADD(Day,@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,'1934-03-01'UNION?ALL??
????SELECT?4,'1966-04-01'UNION?ALL??
????SELECT?5,'1997-05-01'UNION?ALL??
????SELECT?6,'1922-11-21'UNION?ALL??
????SELECT?7,'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,@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,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),?
????CONVERT(char(8),@dt)*3-Month(@dt)-2,@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/05/01'??
--定义年表?
declare?@NB?table(代码?int,年份?int)?
insert?into?@NB??
????select?12,2003?union?all??
????select?12,2004?union?all??
????select?22,2001?union?all??
????select?22,2002?union?all??
????select?22,2003??
--定义月表?
declare?@YB?table(代码?int,年份?int,月份?varchar(2))?
insert?into?@YB??
????select?12,2003,'04'?union?all??
????select?22,2001,'01'?union?all??
????select?22,'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)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.代码,年份=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.月份?
--删除数据处理临时表?
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,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?,1?union?all??
????select?1,'10:00','11:00','装修',null,'2005-1-18','2005-1-19',2?--union?all??
????select?1,2?union?all??
????select?1,'A2',1--union?all??
????select?1,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,?endtime=convert(char(5),endtime,108)?from?tb??
????????????????where?(@week?between?startweekday?and?endweekday)or(@date?between?startdate?and?enddate)?
????????????????order?by?qid,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',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,''))?
????????????????SELECT?@s=','+CAST(@col2?as?varchar),@col1_old=@col1??
????????????END??
????????FETCH?tb?INTO?@col1,@col2??
????END??
INSERT?@t?VALUES(@col1_old,''))?
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',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,''))?
????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,3?--?
*/?
GO?
/*==============================================*/?
--====================================================================?
--3.3.3?使用临时表实现字符串合并处理的示例?
--处理的数据?
CREATE?TABLE?tb(col1?varchar(10),1?
UNION?ALL?SELECT?'a',2?
UNION?ALL?SELECT?'b',1?
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,3?
--*/?
--得到最终结果?
SELECT?col1,col2=MAX(col2)FROM?#t?GROUP?BY?col1?
/*--结果?
col1???????col2?
----------?-----------?
a??????????1,3?
--*/?
--删除测试?
DROP?TABLE?tb,#t?
GO?
?
?
/*==============================================*/?
--===============================================================?
?
--3.3.4.1?每组?<=2?条记录的合并?
--处理的数据?
CREATE?TABLE?tb(col1?varchar(10),2?
UNION?ALL?SELECT?'c',?
????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?
c??????????3?
--*/?
?
--3.3.4.2?每组?<=3?条记录的合并?
--处理的数据?
CREATE?TABLE?tb(col1?varchar(10),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,3?
c??????????3?
--*/? GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
