SQL server2005常用的函数和分页的几个解决方案

SQL server2005常用的函数和分页的几个解决方案：

使用时间和日期的函数
getdate():获取系统当前时间
dateadd(datepart,number,date):计算在一个时间的基础上增加一个时间后的新时间值,比如：dateadd(yy,30,getdate())
datediff(datepart,startdate,enddate):计算两个时间的差值,比如：datediff(yy,getdate(),'2008-08-08')
dataname(datepart,date):获取时间不同部分的值，返回值为字符串
datepart(datepart,date):和datename相似，只是返回值为整型
day(date):获取指定时间的天数
month(date):获取指定时间的月份
year(date):获取指定时间的年份

问题1:
表A是一个不断有新记录增加的表，字段t就是记录的插入时间,现在想知道每10秒钟插入到该表的
记录总数大于N条的时间和记录条数，并按由大到小的顺序排序，请写出完成该功能的SQL语句。
解决方案：
declare @t datetime
select @t=min(t) from B
select max(t) as maxT,min(t) as minT,count(*) as Num from B group by
datediff(ss,@t,t)/10 having count(*)>1 order by count(*) desc

全局变量：
以@@开头，且不能由用户自定义，下面来看几个常用的全局变量：
@@rowcount ：表示最近一个语句影响的行数。
@@error:上一条sql语句返回的错误信息。
@@identity:对有标识列的表而言，系统会为表每生成一个新的标识号，这个变量记录的就是最近产生的id。

rowcount:会话级全局变量,比如set rowcount 3 ,就是设置返回的记录数最多是3条，此变量的作用类似于
top子句，TOP 子句适用于指定了该子句的单个 SELECT 语句。SET ROWCOUNT 将一直有效，直到执行另一个
SET ROWCOUNT 语句，例如 SET ROWCOUNT 0 将关闭该选项。

indetity_insert：会话级全局变量
任何时候，一个会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设
置为 ON，则对另一个表发出 SET IDENTITY_INSERT ON 语句时，SQL Server 2005 将返回一个错误信息，
指出 SET IDENTITY_INSERT 已设置为 ON，并报告已将其属性设置为 ON 的表。
如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。
SET IDENTITY_INSERT 的设置是在执行或运行时设置的，而不是在分析时设置的。
dbcc checkident (table_name,reseed,n)：此语句的功能是更正列的当前标识值，把table_name的标
识符设置为n。如果当标识列中存在 PRIMARY KEY 或 UNIQUE 约束时，无效标识信息会导致错误消息 2627。

SQL Server的几个分页解决方案：
解决方案1：
declare @pageIndex int,@pageSize int,@recordNum int
set @pageIndex=3
set @pageSize=3
select top(@pageSize) * from Grade where id not in (select top
((@pageIndex-1)*@pageSize) id from Grade)//显示出第 7 到 第 9 条

select @recordNum=count(*) from Grade//显示总记录数

点评：效率不高，而且取出来的数据是按id排序的。如果想按其他字段排序就不行。

解决方案二：
declare @id int
--set rowcount 3
--select @id=id from Grade
select top 3 @id=id from Grade//这句等效于上面注视的两句
select top 3 * form Grade where id>@id//查询出第 4 到第 6 条记录

点评：效率比方案一稍高，但是取出来的数据是也按id排序的。如果想按其他字段排序就不行

解决方案三：
create table #table(new_id int identity(1,1) primary key,id int)
insert into #table(id) select id from grade
select a.* from grade a join #table b  on (b.new_id between 6 and 9) and a.id=b.id
//取出第 6 到第 9 条，
点评：其原理是把要分页的表的主键插入到临时表中，临时表的字段就是一个标识列和要分
页的表的主键列（包括是多个主键的情况，上面的两种方法就不行）。特点是效率高，取出
来的数据能根据自己要求的字段排序。

临时表：
上面的解决方案三就是使用了临时表，它与永久表相似，但临时表存储在 tempdb 中，当不
再使用时会自动删除。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表
的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server
 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户
都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

表级变量：
象上面分页的例子也可以使用表级变量，象下面这样：

declare @table table (newid int identiey(1,1),id int)
insert into @table(id) select id from grade
select a.* from grade a join @table b  on (b.new_id between 6 and 9) and a.id=b.id
这种方式比使用临时表分页效率更高。

字符串函数：
left(stringExp,intExp):取字符串左边多少个字符
len(strExp):计算指定字符串的长度
char(intExp):根据指定字符的ASCII码返回相应的字符
ascii(strExp)：将一个字符转换成相应的ascii码
lower(strExp):将字符串转换为小写
Upper(strExp):将字符串转换为大写
Ltrim(strExp):去字符串左边的空格
rtrim(strExp):去字符串右边的空格
substring(strExp,intExp,intExp):按指定的索引截取一个字符串的子字符串
replace(strExp,strOldStr,strNewStr):将字符串中的部分内容用新的字符串来代替

系统其他转换函数:
isnull(逻辑表达式,代替值):判断逻辑表达式是否为null，是的话就用指定的值代替。
convert()和cast：
CAST 函数和 CONVERT 函数还可用于获取各种特殊数据格式，并可用于选择列表、WHERE
子句以及允许使用表达式的任何位置中。如果希望 Transact-SQL 程序代码符合 SQL-92，
请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的功能，请使用 CONVERT
而不要使用 CAST。
使用 CAST 或 CONVERT 时，需要提供以下信息：
要转换的表达式；例如，销售报表要求销售数据从货币数据转换为字符数据。
要将指定的表达式转换为的数据类型，例如 varchar 或其他 SQL Server 系统数据类型。
除非将被转换的值存储起来，否则转换仅在 CAST 函数或 CONVERT 函数的作用时间范围内有效。
如果转换时没有指定数据类型的长度，则 SQL Server 自动将 30 作为长度值。

问题：
有一张商品表，里面三个字段：购买日期，购买人，价格。现在要统计每个购买人每一
季度的消费总价，并算出小计（也就是这一年消费的总价）

解决方案：

创建一张shop表：
create table shop
(
 selldate datetime default(getdate()),
 name varchar(20),
 price money,
)
加入一些测试数据：
insert into shop(selldate,name,price) values('2006-05-12','张三',75)
insert into shop(selldate,name,price) values('2006-07-12','张三',50)
insert into shop(selldate,name,price) values('2006-09-12','张三',43)
insert into shop(selldate,name,price) values('2006-11-12','李四',67)
insert into shop(selldate,name,price) values('2006-01-12','李四',98)
insert into shop(selldate,name,price) values('2006-06-12','李四',100)
insert into shop(selldate,name,price) values('2006-08-12','王五',134)
insert into shop(selldate,name,price) values('2006-04-12','王五',165)
insert into shop(selldate,name,price) values('2006-10-12','王五',85)
insert into shop(selldate,name,price) values('2006-08-12','赵六',11)
insert into shop(selldate,name,price) values('2006-12-12','赵六',165)
insert into shop(selldate,name,price) values('2006-02-12','赵六',17)
insert into shop(selldate,name,price) values('2006-01-12','赵六',198)

select * from shop

解决问题的select语句：
select name 姓名,
sum(case when datepart(q,selldate)=1 then price else 0 end) 第一季度,
sum(case when datepart(q,selldate)=2 then price else 0 end) 第二季度,
sum(case when datepart(q,selldate)=3 then price else 0 end) 第三季度,
sum(case when datepart(q,selldate)=4 then price else 0 end) 第四季度,
sum(price) 小计
from shop  group by name