个人整理摘录的SQL2005新增几种常用的T
-
SQL语句
--
SQL2005 分页
select * from ( select id, title, ROW_NUMBER() OVER ( order by [ id ] desc ) as row from fabu) a
where row between 1000 and 1020
-- SQL2005 排位
select * from ( select id, title, RANK() OVER ( order by [ id ] desc ) as rank from fabu) a
where rank between 1000 and 1020
-- top.可以动态传入参数,省却了动态SQL的拼写。
DECLARE @top int ;
set @top = 10 ;
select top ( @top ) id from fabu
/**/ /*Apply.对递归类的树遍历很有帮助 CROSS APPLY 仅返回外部表中通过表值函数生成结果集的
行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列
中的值为 NULL.*/
-- CTE.创建临时表,使阅读清晰,非常有时代感
WITH Sales_CTE (id, date, title)
AS
(
SELECT top 10 id,addtime,title FROM fabu order by id asc
)
select top 5 id,title from Sales_CTE ORDER BY [ id ] desc ;
-- try/catch 代替了原来VB式的错误判断。比Oracle高级不少
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1 / 0 ;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
-- pivot/unpivot使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表
exec sp_dbcmptlevel ' gwtest ' , ' 90 '
SELECT [ id ] ,title,addtime
FROM
( SELECT top 10 [ id ] ,title,addtime,userid from fabu order by id) p
PIVOT
(
COUNT (userid)
FOR userid IN
( [ 4 ] , [ 222 ] , [ 218 ] )
) AS pvt
ORDER BY [ id ]
select * from ( select id, title, ROW_NUMBER() OVER ( order by [ id ] desc ) as row from fabu) a
where row between 1000 and 1020
-- SQL2005 排位
select * from ( select id, title, RANK() OVER ( order by [ id ] desc ) as rank from fabu) a
where rank between 1000 and 1020
-- top.可以动态传入参数,省却了动态SQL的拼写。
DECLARE @top int ;
set @top = 10 ;
select top ( @top ) id from fabu
/**/ /*Apply.对递归类的树遍历很有帮助 CROSS APPLY 仅返回外部表中通过表值函数生成结果集的
行。OUTER APPLY 既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列
中的值为 NULL.*/
-- CTE.创建临时表,使阅读清晰,非常有时代感
WITH Sales_CTE (id, date, title)
AS
(
SELECT top 10 id,addtime,title FROM fabu order by id asc
)
select top 5 id,title from Sales_CTE ORDER BY [ id ] desc ;
-- try/catch 代替了原来VB式的错误判断。比Oracle高级不少
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1 / 0 ;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
-- pivot/unpivot使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表
exec sp_dbcmptlevel ' gwtest ' , ' 90 '
SELECT [ id ] ,title,addtime
FROM
( SELECT top 10 [ id ] ,title,addtime,userid from fabu order by id) p
PIVOT
(
COUNT (userid)
FOR userid IN
( [ 4 ] , [ 222 ] , [ 218 ] )
) AS pvt
ORDER BY [ id ]