sql 2005 T-sql脚本增强集合贴

SQL Server 2005中的窗口函数（window function）与微软Windows无关；相反，它们建立数据窗口。窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地恢复累计总数、移动平均值、以及执行其它计算。一个SQL Server窗口是对函数应用的行的分区。你使用OVER(…)子句指定一个窗口，你可以对任何一个聚合函数应用这个子句。通常来说，你把数据分成几个组，但OVER（）的自变量可选。

窗口函数功能非常强大，建立起来也十分容易。你可以使用这个技巧立即搜索到大量统计值。这个教程中的例子指出(以AdventureWorks数据库为示例)，你可以在单独一个查询中搜索任何统计值集合。

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty AS 'Item Qty'
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Qty'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average Qty'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Minimum Count'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Maximum Count'
      ,LineTotal
      ,AVG(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Average Amount'
      ,SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS 'Total Amount'
      ,(LineTotal/SUM(LineTotal) OVER(PARTITION BY SalesOrderID)) * 100 AS 'Percent'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43664
ORDER BY ProductIDSQL2005新增函数，排名函数

--sql2005排名函数
SELECT ROW_NUMBER() OVER (ORDER BY id) AS 行号,* FROM sysobjects

--在2000版本中要这样实现
SELECT Identity(int,1,1) AS Num,* INTO #temptable FROM TableName
SELECT＊FROM #temptable
DROP TABLE #temptable排名函数备注说明：

RANK()函数保留列表中行的位置序号，对于每个重复值，这个函数跳过下面值，于是下一个不重复的值就保留在了正确的位置上了。

DENSE_RANK()函数工作方式是相同的，不过它不跳过每个连带之后的数字，这样就不会有数字消失了，不过排列序号位置出现连带的地方就丢失了。举例（在做学生成绩排名之类的时非常有作用。这两个非常有用）

NTITLE(n)函数，将结果切分为有限数量的排列组。更多示例见联机丛书.

示例2：在SQL2005中处理交叉表: 关键字:PIVOT 和 UNPIVOT 运算符

DECLARE @t TABLE ([日期] datetime,[时间] varchar(20),[售货金额] int)
insert into @t select '2006-01-02','早上',50
union all select '2006-01-02','中午',20
union all select '2006-01-02','晚上',30
union all select '2006-01-02','零晨',40
union all select '2006-01-03','早上',40
union all select '2006-01-03','中午',60
union all select '2006-01-03','晚上',50
union all select '2006-01-03','零晨',50
union all select '2006-01-04','早上',80
union all select '2006-01-04','中午',60
union all select '2006-01-04','晚上',20
union all select '2006-01-04','零晨',40

--查询

select * ,金额小计=(select sum(售货金额) from @t where 日期=PT.日期 ) from @t as TAB
PIVOT
( max([售货金额])
for [时间] in ([早上],[中午],[晚上],[零晨])
) as PT

--列不确定时：

DECLARE @S VARCHAR(MAX)
SET @S=''
SELECT @S=@S+',['+时间+']' FROM @t
GROUP BY 时间
SET @S=STUFF(@S,1,1,'')
EXEC('
select 日期,'+@S+',金额小计=(select sum(售货金额) from @t where 日期=PT.日期 ) from @t as TAB
PIVOT
( max(售货金额)
for 时间 in ('+@S+')
) as PT
')SQL2005中拆分列值，使用OUTER APPLY运算符。见示例：

--查询

select * ,金额小计=(select sum(售货金额) from @t where 日期=PT.日期 ) from @t as TAB
PIVOT
( max([售货金额])
for [时间] in ([早上],[中午],[晚上],[零晨])
) as PT

/*结果

日期                早上    中午   晚上   零晨   金额小计
----------------------- ----------- ----------- ----------- ----------- -----------
2006-01-02 00:00:00.000 50          20          30          40          140
2006-01-03 00:00:00.000 40          60          50          50          200
2006-01-04 00:00:00.000 80          60          20          40          200 */SQL2005中合并列值，见示例

-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/* 结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc */更多见：http://blog.csdn.net/itblog/archive/2006/06/05/774363.aspx

sql 2005 express版本,需要加增加图形管理器，下载：http://www.microsoft.com/downloads/details.aspx?FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e&DisplayLang=zh-cn

sql2005导入导出向导: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe 可自己在工具--外部工具中--添加引用.好比快捷方式。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhou__zhou/archive/2007/06/17/1655352.aspx

posted @ 2009-07-03 14:49 周骏阅读(382) 评论(0) 编辑收藏举报

刷新页面返回顶部

周骏

sql 2005 T-sql脚本增强 集合贴

公告

sql 2005 T-sql脚本增强集合贴