MSSQL编程：T-SQL 天生一个浅易的 公积年历 T-SQL ...
MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。--增添了日期地点月及年的周次!
--日曜日要算在"上一周"!(注重WeekOfYear、WeekOfMonth与MyWeekOfYear、MyWeekOfMonth的区分)
--注重datename的值会因SQLServer言语版本或日期格局有所差别!
--本测试情况为:SQLServer2000简体中文版+Windows简体中文版
declare@datetime
set@=1995-02-2511:00:50--1995-01-01恰好是个日曜日
select@as日期
,dateadd(year,datediff(year,0,@),0)as地点年的第一天
,dateadd(year,1+datediff(year,0,@),0)-1as地点年的最初一天
,dateadd(quarter,datediff(quarter,0,@),0)as地点季的第一天
,dateadd(quarter,1+datediff(quarter,0,@),0)-1as地点季的最初一天
,dateadd(month,datediff(month,0,@),0)as地点月的第一天
,dateadd(month,1+datediff(month,0,@),0)-1as地点月的最初一天
,dateadd(week,datediff(week,0,@),0)as地点周的第一天
,dateadd(week,1+datediff(week,0,@),0)-1as地点周的最初一天
selectdateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))as
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))as
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))as
,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))asWeekOfYear
,datediff
(
week
,casewhendatename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))=日曜日
thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
elsedateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))
end
,casewhendatename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))=日曜日
thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
elsedateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end
)
+1asMyWeekOfYear
,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+1asWeekOfMonth
,datediff
(week
,casewhendatename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))=日曜日
thendateadd(day,-1,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))
elsedateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
end
,casewhendatename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))=日曜日
thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
elsedateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end
)
+1asMyWeekOfMonth
,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))asDayOfYear
intoD
from
(
select0asi
unionall
select1
unionall
select2
unionall
select3
unionall
select4
unionall
select5
unionall
select6
unionall
select7
unionall
select8
unionall
select9
unionall
select10
unionall
select11
)M
,
(
select0asi
unionall
select1
unionall
select2
unionall
select3
unionall
select4
unionall
select5
unionall
select6
unionall
select7
unionall
select8
unionall
select9
unionall
select10
unionall
select11
unionall
select12
unionall
select13
unionall
select14
unionall
select15
unionall
select16
unionall
select17
unionall
select18
unionall
select19
unionall
select20
unionall
select21
unionall
select22
unionall
select23
unionall
select24
unionall
select25
unionall
select26
unionall
select27
unionall
select28
unionall
select29
unionall
select30
)d
wheredatediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))=m.i
orderby
selectmonth(min(date))
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=日曜日anddatediff(month,min(a.date),date)=0)as日曜日
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=礼拜一anddatediff(month,min(a.date),date)=0)as礼拜一
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=礼拜二anddatediff(month,min(a.date),date)=0)as礼拜二
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=礼拜三anddatediff(month,min(a.date),date)=0)as礼拜三
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=木曜日anddatediff(month,min(a.date),date)=0)as木曜日
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=礼拜五anddatediff(month,min(a.date),date)=0)as礼拜五
,(selectmin(date)
fromd
wheredatepart(week,date)=datepart(week,a.date)anddatename(weekday,date)=礼拜六anddatediff(month,min(a.date),date)=0)as礼拜六
fromda
groupbydatediff(month,0,date),datepart(week,date)
提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。 也可谈一下你是怎么优化存储过程的? 外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。 另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程); 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。 如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。 但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。
页:
[1]