如下面的例子: USE demo GO CREATE TABLE rankorder ( orderid INT, qty INT ) GO INSERT rankorder VALUES(30001,10) INSERT rankorder VALUES(10001,10) INSERT rankorder VALUES(10006,10) INSERT rankorder VALUES(40005,10) INSERT rankorder VALUES(30003,15) INSERT rankorder VALUES(30004,20) INSERT rankorder VALUES(20002,20) INSERT rankorder VALUES(20001,20) INSERT rankorder VALUES(10005,30) INSERT rankorder VALUES(30007,30) INSERT rankorder VALUES(40001,40) GO SELECT orderid,qty, ROW_NUMBER() OVER(ORDER BY qty) AS rownumber, RANK() OVER(ORDER BY qty) AS rank, DENSE_RANK() OVER(ORDER BY qty) AS denserank FROM rankorder ORDER BY qty 还有一点要说明: select ROW_NUMBER() OVER (order by aa) from tbl order by bb 会先执行aa的排序,然后再进行bb的排序。 可能有的朋友会抱怨集成的order by,其实如果使用ranking函数,Order by是少不了的。如果担心Order by会影响效率,可以为order by的字段建立聚集索引,查询计划会忽略order by 操作(因为本来就是排序的嘛)。 2、top 可以指定一个数字表达式,以返回要通过查询影响的行数或百分比,还可以根据情况使用变量或子查询。可以在DELETE、UPDATE和INSERT查询中使用TOP选项。可以动态传入参数,省却了动态SQL的拼写。 例: --声明个变量 DECLARE @a INT DECLARE @b INT DECLARE @c INT
--赋值 SET @a = 10 SET @b = 5 SELECT @c = @a/@b
--使用计算表达式 SELECT TOP(@c) * FROM toptest --使用SELECT语句作为条件 SELECT TOP(SELECT COUNT(*) FROM toptest2) * FROM toptest
--指出top DELETE TOP(2) toptest where column1>'t6'
--更新top UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'
3、Apply 对递归类的树遍历很有帮助。 CROSS APPLY : 得到和FUNCTION 结果相匹配的记录 OUTER APPLY: All rows , regardless of matching function results 一个有代表性的例子: USE demo GO CREATE TABLE Arrays ( aid INT NOT NULL IDENTITY PRIMARY KEY, array VARCHAR(7999) NOT NULL ) GO INSERT INTO Arrays VALUES('') INSERT INTO Arrays VALUES('10') INSERT INTO Arrays VALUES('20,40,30') INSERT INTO Arrays VALUES('-1,-3,-5') GO CREATE FUNCTION function1(@arr AS VARCHAR(7999)) RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL) AS BEGIN DECLARE @end AS INT, @start AS INT, @pos AS INT SELECT @arr = @arr + ',', @pos = 1, @start = 1, @end = CHARINDEX(',', @arr, @start) WHILE @end > 1 BEGIN INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start)) SELECT @pos = @pos + 1, @start = @end + 1, @end = CHARINDEX(',', @arr, @start) END RETURN END
--测试 SELECT * FROM function1('200,400,300') GO
SELECT A.aid, F.* FROM Arrays AS A CROSS APPLY function1(array) AS F GO SELECT A.aid, F.* FROM Arrays AS A OUTER APPLY function1(array) AS F GO 查询结果:
4、CTE(Common Table Expression 通用表达式) 它是一个可以由定义语句引用的临时表命名的结果集,要用到WITH 关键字。例: WITH SalesCTE(ProductID,SalesOrderID) AS (SELECT ProductID,COUNT(SalesOrderid) FROM Sales.SalesOrderDetail GROUP BY ProductID) SELECT * FROM SalesCTE 5、try/catch 先说明一下事务相关语法: BEGIN TRAN[SACTION] [transaction_name] COMMIT [TRAN[SACTION]] [transaction_name] ROLLBACK [TRAN[SACTION] [transaction_name] SAVE TRAN[SACTION] [savepoint_name] 上面这几很简单不解释了,下面看 SET XACT_ABORT ON/OFF,当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL 语句产生运行时错误,整个事务将终止并回滚。为 OFF 时,只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。所以在sql server 2k里使用事务就必须SET XACT_ABORT ON或者设置保存点。有了try/catch sql server2005 就不一样了!! 演示代码如下表。
USE demo GO CREATE TABLE student --创建工作表 ( stuid INT NOT NULL PRIMARY KEY, stuname VARCHAR(50) ) CREATE TABLE score ( stuid INT NOT NULL REFERENCES student(stuid), score INT ) GO INSERT INTO student VALUES (101,'zhangsan') INSERT INTO student VALUES (102,'wangwu') INSERT INTO student VALUES (103,'lishi') INSERT INTO student VALUES (104,'maliu') 2K事务的打操作 2005 使用TRY...CATCH构造扩充了错误处理能力 --调用一个运行时错误 SET XACT_ABORT ON(若为OFF 事务不会回滚) BEGIN TRAN INSERT INTO score VALUES (101,90) INSERT INTO score VALUES (102,78) INSERT INTO score VALUES (107,76) /*外键错误*/ INSERT INTO score VALUES (103,81) INSERT INTO score VALUES (104,65) COMMIT TRAN GO SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO score VALUES (101,90) INSERT INTO score VALUES (102,78) INSERT INTO score VALUES (107,76) /*外键错误*/ INSERT INTO score VALUES (103,81) INSERT INTO score VALUES (104,65) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK PRINT '事务回滚' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_MESSAGE() as ErrorMessage; END CATCH GO