实例:用SQLServer2005内置工具建立审查系统:能够得到,如谁访问了我们的数据、如何批准访问权、以及我们如何对访问进行监控,以防止某些人入侵、登录数据或做他们不该做的事情。引用这里
在SQL2005中处理交叉表: 关键字:PIVOT 和 UNPIVOT
示例:
--列不确定时:
SQL2005中拆分列值,借用XML,简单。见示例:
结果:
1 aa
1 bb
2 aaa
2 bbb
2 ccc
引用 :http://blog.csdn.net/itblog/archive/2006/06/05/774358.aspx
SQL2005中合并列值,见示例
更多见:http://blog.csdn.net/itblog/archive/2006/06/05/774363.aspx
SQL2005新函数, 排列函数 :
示例1 : 在结果集中显示行号 ROW_NUMBER ( )函数 语法:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause > )
返回结果集:
行号 对象名称
1 sp_MSalreadyhavegeneration
2 sp_MSwritemergeperfcounter
3 TABLE_PRIVILEGES
在2000版本中要这样实现
示例2: RANK() 函数保留列表中行的位置序号,对于每个重复值,这个函数跳过下面值,于是下一个不重复的值就保留在了正确的位置上了。
DENSE_RANK() 函数工作方式是相同的,不过它不跳过每个连带之后的数字,这样就不会有数字消失了,不过排列序号位置出现连带的地方就丢失了。
举例(在做学生成绩排名之类的时非常有作用。这两个非常有用)
示例3: NTITLE(n) 函数,将结果切分为有限数量的排列组。更多示例见联机丛书.
sql 2005 express版本,需要加增加图形管理器,下载 ,express版本中默认没有,sql2005 功能包 列表
sql2005导入导出向导 : C:/Program Files/Microsoft SQL Server/90/DTS/Binn/DTSWizard.exe 可自己在工具--外部工具中--添加引用.好比快捷方式。
在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
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
' )
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中拆分列值,借用XML,简单。见示例:
--
示例数据
DECLARE @t TABLE (id int , [ values ] varchar ( 100 ))
INSERT @t SELECT 1 , ' aa,bb '
UNION ALL SELECT 2 , ' aaa,bbb,ccc '
-- 查询处理
SELECT A.id, B.value
FROM (
SELECT id, [ values ] = CONVERT (xml,
' <root><v> ' + REPLACE ( [ values ] , ' , ' , ' </v><v> ' ) + ' </v></root> ' )
FROM @t
)A
OUTER APPLY(
SELECT value = N.v.value( ' . ' , ' varchar(100) ' )
FROM A. [ values ] .nodes( ' /root/v ' ) N(v)
)B
DECLARE @t TABLE (id int , [ values ] varchar ( 100 ))
INSERT @t SELECT 1 , ' aa,bb '
UNION ALL SELECT 2 , ' aaa,bbb,ccc '
-- 查询处理
SELECT A.id, B.value
FROM (
SELECT id, [ values ] = CONVERT (xml,
' <root><v> ' + REPLACE ( [ values ] , ' , ' , ' </v><v> ' ) + ' </v></root> ' )
FROM @t
)A
OUTER APPLY(
SELECT value = N.v.value( ' . ' , ' varchar(100) ' )
FROM A. [ values ] .nodes( ' /root/v ' ) N(v)
)B
1 aa
1 bb
2 aaa
2 bbb
2 ccc
引用 :http://blog.csdn.net/itblog/archive/2006/06/05/774358.aspx
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
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
SQL2005新函数, 排列函数 :
示例1 : 在结果集中显示行号 ROW_NUMBER ( )函数 语法:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause > )
SELECT ROW_NUMBER() OVER (ORDER BY id) AS 行号,* FROM sysobjects
行号 对象名称
1 sp_MSalreadyhavegeneration
2 sp_MSwritemergeperfcounter
3 TABLE_PRIVILEGES
在2000版本中要这样实现
SELECT
Identity
(
int
,
1
,
1
)
AS
Num,
*
INTO
#temptable
FROM
TableName
SELECT * FROM #temptable
DROP TABLE #temptable
SELECT * FROM #temptable
DROP TABLE #temptable
示例2: RANK() 函数保留列表中行的位置序号,对于每个重复值,这个函数跳过下面值,于是下一个不重复的值就保留在了正确的位置上了。
DENSE_RANK() 函数工作方式是相同的,不过它不跳过每个连带之后的数字,这样就不会有数字消失了,不过排列序号位置出现连带的地方就丢失了。
举例(在做学生成绩排名之类的时非常有作用。这两个非常有用)
示例3: NTITLE(n) 函数,将结果切分为有限数量的排列组。更多示例见联机丛书.
sql 2005 express版本,需要加增加图形管理器,下载 ,express版本中默认没有,sql2005 功能包 列表
sql2005导入导出向导 : C:/Program Files/Microsoft SQL Server/90/DTS/Binn/DTSWizard.exe 可自己在工具--外部工具中--添加引用.好比快捷方式。