mssql2005 新增的T-SQL语句，最好有详细例子

SQL2005 OUTPUT的新用法http://blog.csdn.net/htl258/archive/2009/09/19/4568708.aspxCROSS APPLY $ OUTER APPLY ....http://blog.csdn.net/htl258/category/524113.aspx建议到MY Blog看看

SQL SERVER 2005已经出来好长时间了,一直没有静下心来仔细研究研究,最近刚刚弄了一个English Enterprise版本,把自己学到的和大家一起分享吧1.表的同义名(Synonyms) 为表创建同义名,这样就可以在Select语句里边使用表的同义名了.注意在更改表的定义的时候只能使用表的原名,而不能使用表的同义名.--创建同义名CREATE SYNONYM syn_OR
FOR dbo.Orders--使用同义名查询表select * from syn_OR--删除同义名DROP SYNONYM syn_OR2.INTERSECT 操作符INTERSECT操作符就是取表集A和表集B的交集.--查询,Northwind数据库select * from orders
intersect
select * from orders
where customerid='vinet'3.varchar(max),nvarchar(max),varbinary(max)数据类型.还记得在大文本的时候,我们被迫使用text数据类型带来的不便吗?这回好了,2005带来的新的数据类型兼容以前的varchar数据类型,但是存储的空间更大了.varchar(max)和varbinary(max)可以存储231,而nvarchar(max)可以存储230字节,够用了吧!--  创建测试表create table #t(id int,Study_Varcharmax varchar(max))--  插入数据
insert #t select 1,'Imagine this is a very long non-Unicode string'
insert #t select 2,'Imagine this is another very long non-Unicode string'--  使用.write语法来进行更新update #t
set Study_Varcharmax .write('n incredibly', 17, 5)
where id = 1--  查询结果.
select * from #t
当然varchar(max)完全兼容以前的varchar数据类型,比如你可以在游标的fetch语句取得它的值,使用.write更新,在触发器的inserted或者deleted里边引用,还可以使用字符串函数如,len,substr等.
4.Top后边的参数.还记得Top后边不能使用参数给我们带来的不便吗?2005终于可以在Top后边使用参数了,快来和我一起试验吧!DECLARE @n int
SET @n = 10SELECT TOP(@n) *
FROM Products
ORDER BY UnitPrice DESC5.获取DML语句所影响的行.以前我们只能通过触发器来获取删除或者更新语句所影响的行的信息,现在我们只要使用新的Output语法,就能轻松的获取DML语句所影响的行的信息.--  创建测试表,并插入测试数据create table #t(id int,name varchar(200))insert #t select 1,'first'
insert #t select 2,'second'--  使用表变量来保存输出结果集.declare @tb_t table(id int,name varchar(200))update #t set name='abc'
output inserted.id,inserted.name into @tb_t
where id=1select * from @tb_t6.新增的Some,Any,All运算符.语法:<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
        {SOME | ANY} {subquery}
子查询返回一个结果集,scalar_expression和结果集任何一个比较成功,就返回True.<scalar_expression> { = | <> | != | > | >= | !> | < | <= | !<}
        All {subquery}自查询返回一个结果集,scalar_expression只有和结果集所有比较成功,才返回True.刚接触这个运算符,感觉和in比较类似,而实际上如果合理运用,这个运算符的功能是非常强大的.select * from Products
where CategoryID=any(select categoryid                                           from dbo.Categories                                           where categoryName in ('Produce','Seafood'))
7.PIVOT and UNPIVOTPIVOT and UNPIVOT的功能相信大家都已经熟悉了,我们结束了用拼凑SQL字符串的方式来进行行列转换的时代.但是Pivot的功能不只如此,我觉得它最大的功能是能让我们灵活的对数据表进行聚集分析,就像OLAP多维数据集那样.-- Northwind的Order表:统计每个雇员在1996,1997,1998的定单数量.select EmployeeID,[1996] as Y_1996,[1997] as Y_1997,[1998] as Y_1998
from
(
select EmployeeID,Year(OrderDate) as Year_Order,OrderID
from orders
) t
pivot
(count(OrderID) for Year_Order in ([1996],[1997],[1998])) pvt
order by EmployeeID8.表值函数增强功能.以前我们使用表值函数,只能用select * from 表值函数的形式,如果我们想和别的表连接就做不到了.现在2005新增的{CROSS | OUTER} APPLY运算符可以让我们轻松的做到这一点.下边我就以一个例子来说明.表1(t):sail  cmd
9258  LBQR|LB
92587  A|19|1
想变成如下的形式:sail  cmd
9258  LBQR
9258  LB
92587  A
92587　19
92587　1
-- 步骤1:表值函数create function dbo.fn_t(@sail int,@cmd varchar(1000))
returns @tb_t table(sail int,cmd varchar(1000))
as
begin
        while(charindex('|',@cmd)>0)  -- 循环取得列表值,并插入@tb_t表里边.
        begin
                insert @tb_t select @sail,left(@cmd,charindex('|',@cmd)-1)
                set @cmd=stuff(@cmd,1,charindex('|',@cmd),'')
        end
        insert @tb_t select @sail,@cmd  -- 插入最后一个值
return
end
Go--  步骤2:查询select t.sail,b.cmd
from  t
    cross apply  dbo.fn_t(t.sail,t.cmd) b
order by  t.sail 9.新的Ranking函数.a.Row_Number()函数--  Northwind表：对顾客的City进行排名select Row_Number() over(Order By City) as RowID,*
from Customers--  Northwind表:对国家内的城市进行排名select Row_Number() over(Partition by Country Order By City),*
from Customers
b.Dense_Rank()函数总的来说，Dense_Rank()函数和Row_Number()函数的功能是类似的，只不过在排名结果上略有不同．在有相同的值的时候，Dense_Rank()排名是连续的，而Row_Number()排名是跳跃的．10.错误处理.(try catch)还记得我们在SQL脚本里边使用大量的if  @@error>0这样的语句来进行错误检测吗?现在好了,2005的try catch语法可以让我们轻松的进行错误检测.注意try catch 不能检测严重级小于等于10或者的错误,因为这样的错误只是一些警告信息.也不能检测严重级别大于20的的错误,因为这样的错误会终止数据库引擎的工作.--  创建测试表create table #t(id int identity(1,1),name varchar(100) not null)--  执行脚本
begin try
insert #t(name) select Null  --插入空值,违反了非空的约束条件.
end try
begin catch
select ERROR_NUMBER(  ) ErrorNumber,
           ERROR_STATE(  ) ErrorState,
           ERROR_SEVERITY(  ) ErrorSeverity,
           ERROR_MESSAGE(  ) ErrorMessage
end catch
11.DDL触发器.我记得以前我们总是为一个表的突然失踪而苦恼,不知道这个表被谁给删除了.现在好了,使用DDL触发器,我们可以追踪表的去向了.--  测试表create table t(id int identity(1,1),name varchar(100) not null)--  日志记录表.记住详细信息是存储在xml类型中.create table DB_Log(F_LogID int identity(1,1),F_Content xml not null)--  创建触发器.create trigger tri_t
on database
for create_table,drop_table
as
insert into DB_Log values(EventData())
Go--删除表drop table tselect * from DB_Log--  追踪信息.看起来信息非常详细的.足够我们把"凶手"追查出来的.<EVENT_INSTANCE>
  <EventType>DROP_TABLE</EventType>
  <PostTime>2006-05-13T09:11:15.767</PostTime>
  <SPID>54</SPID>
  <ServerName>LILY\STUDY2005</ServerName>
  <LoginName>LILY\Administrator</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>Northwind</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>t</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>drop table t
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>
12.使用OpenRowSet来读取数据文件.以前我们只能用OpenRowSet来读取别的数据源的数据,但是对于数据文件的读取就不那边方便了.新的OpenRowSet语法增加了Bulk功能,可以让我们直接从数据文件里边读取数据.--  生成用于测试的数据文件和格式文件.(以Northwind的Customers表为例)Bcp Northwind.dbo.Customers out D:\Customer.txt  -T -cBcp Northwind.dbo.Customers format nul -f D:\Customer.fmt  -T -c--  使用OpenRowSet来读取刚才生成的数据文件.select *from OpenRowSet(Bulk N'D:\Customer.txt',FormatFile=N'D:\Customer.fmt') as t13.外连接的另类语法.以前内连接我们可以使用where来代替,但是外连接就没有这样的语法了.2005新推出的*=和=*让我们做到了这点.--  Northwindselect a.*,b.*
from Products a,Categories b
where a.CategoryID*=b.CategoryID
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/mschen/archive/2006/05/10/722372.aspx另转一个

SQL Server 2008 新增T-SQL 简写语法时间:2009-07-16 14:50:12来源:网络作者:未知点击:104次
1.定义变量时可以直接赋值
1.定义变量时可以直接赋值DECLARE @Id int = 52.Insert 语句可以一次插入多行数据INSERT INTO StateList VALUES(@Id, 'WA'), (@Id + 1, 'FL'), (@Id + 2, 'NY')3.支持+=操作符SET StateId += 1完整示例如下：view plaincopy to clipboardprint?CREATE TABLE StateList(StateId int, StateName char(2))   GO     -- Declare variable and assign a value in a single statement   DECLARE @Id int = 5     -- Insert multiple rows in a single statement with IDs 5, 6, and 7   INSERT INTO StateList VALUES(@Id, 'WA'), (@Id + 1, 'FL'), (@Id + 2, 'NY')     -- Use compound assignment operator to increment ID values to 6, 7, and 8   UPDATE StateList    SET StateId += 1     -- View the results   SELECT * FROM StateList  CREATE TABLE StateList(StateId int, StateName char(2))
GO-- Declare variable and assign a value in a single statement
DECLARE @Id int = 5-- Insert multiple rows in a single statement with IDs 5, 6, and 7
INSERT INTO StateList VALUES(@Id, 'WA'), (@Id + 1, 'FL'), (@Id + 2, 'NY')-- Use compound assignment operator to increment ID values to 6, 7, and 8
UPDATE StateList
SET StateId += 1-- View the results
SELECT * FROM StateList 结果集为：StateId StateName
------- ---------
6       WA
7       FL
8       NY(3 row(s) affected)

本篇文章来源于：开发学院 http://edu.codepub.com   原文链接：http://edu.codepub.com/2009/0716/10104.php这是2008的，也上一下。

建议你买本 Sqlserver2005 T-Sql 技术内幕

--1、 sql server 2005 中的PIVOT和UNPIVOT中的应用
现有一张表（没有现成的表，临时产生）格式如下：
              机器1  机器2  机器3  机器4  机器5  机器6
组别1       1         2           3         4          5           6
组别2       5         9           7         2          1           3
组别3       9         5           6         4          4           3
现在想转换成如下格式：
                组别1   组别2   组别3
机器1         1            5         9
机器2         2            9         5
机器3         3            7         6
机器4         4            2         4
机器5         5            1         4
机器6         6            3         5--测试环境
declare @t table(zb varchar(10),机器1 int,机器2 int,机器3 int,机器4 int,机器5 int,机器6 int)
insert into @t select '组别1',1,2,3,4,5,6
union all select '组别2',5,9,7,2,1,3
union all select '组别3',9,5,6,4,4,3SELECT * FROM
(
SELECT  Employee,zb,Orders
FROM
   (SELECT zb, 机器1, 机器2, 机器3, 机器4, 机器5, 机器6
   FROM @t) p
UNPIVOT
   (Orders FOR Employee IN
      (机器1, 机器2, 机器3, 机器4, 机器5, 机器6)
)AS unpvt
) T
PIVOT
(MAX(ORDERS)
for zb in ([组别1],[组别2],[组别3])
)as pt
--结果
/**//*
Employee                                                                                                                         组别1         组别2         组别3
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
机器1                                                                                                                              1           5           9
机器2                                                                                                                              2           9           5
机器3                                                                                                                              3           7           6
机器4                                                                                                                              4           2           4
机器5                                                                                                                              5           1           4
机器6                                                                                                                              6           3           3(6 行受影响)--2、ms sql2005 top(@n)declare @n int
set @n=10
SELECT top(@n) percent [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]
      ,[RequiredDate]
      ,[ShippedDate]
      ,[ShipVia]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
SELECT top(@n) [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
from [Orders]update top(@n) [Orders] set [Freight]=[Freight]+10SELECT top(@n) [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
from [Orders]--3、ms sql2005 try... catch ...set xact_abort on
begin try
   select 1/0
end try
begin catch
select Error_number() as ErrorNumber,Error_Message() as ErrorMessage
end catch--4、ms sql2005 With as 公用表表达式with [Orders_CTE](orderid,[Row Number]) as
(
SELECT orderid
      ,Row_Number() over (order by [EmployeeID]) as [Row Number]
  FROM [Northwind].[dbo].[Orders]
)select * from [Orders_CTE]
--递归create table Dept(
DepartNO  varchar(10),
   DepartName  varchar(20),
TopNo    varchar(10))delete from deptinsert into Dept values('001','董事会','0')
insert into Dept values('002','总裁办公室','001')
insert into Dept values('003','财务部','001')
insert into Dept values('004','市场部','002')
insert into Dept values('005','公关部','002')
insert into Dept values('006','销售部','002')
insert into Dept values('007','分销处','006')
insert into Dept values('008','业务拓展处','004')
insert into Dept values('009','销售科','007')
go
select *
from dept
gowith Dept_CTE as
(
select DepartNO,
   DepartName,
TopNo from Dept
where DepartNo='002'
union all
select child.DepartNO,
   child.DepartName,
child.TopNo from Dept_CTE as Parent
join Dept as child
on parent.DepartNO=child.TopNo
) select * from Dept_CTE--5、ms sql2005 Row_Number Rank Ntile
SELECT [EmployeeID]
      ,Row_Number() over (order by [EmployeeID]) as 'Row Number'
      ,Rank() over (order by [EmployeeID]) as 'Rank'
      ,Dense_Rank() over (order by [EmployeeID]) as 'Dense Rank'
      ,Ntile(6) over (order by [EmployeeID]) as 'Ntile'
  FROM [Northwind].[dbo].[Orders]
order by [EmployeeID]--6、ms sql2005 output
update [Northwind].[dbo].[Employees] set country='test1'
  output deleted.*
  --output inserted.*
  where lastname='Davolio'--7、ms sql2005 cross apply--drop function FunOrdergoCreate Function FunOrder(@EmployeeID int,@n int)
returns table as
return
SELECT top(@n) [OrderID],EmployeeID
FROM Orders
where EmployeeID=@EmployeeID
and EmployeeID<>4
go SELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
join FunOrder(1,3) b
on a.[EmployeeID]=b.EmployeeIDgoSELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
cross apply FunOrder(a.EmployeeID,3) bgoSELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
outer apply FunOrder(a.EmployeeID,3) b

--1、 sql server 2005 中的PIVOT和UNPIVOT中的应用
现有一张表（没有现成的表，临时产生）格式如下：
              机器1  机器2  机器3  机器4  机器5  机器6
组别1       1         2           3         4          5           6
组别2       5         9           7         2          1           3
组别3       9         5           6         4          4           3 现在想转换成如下格式：                组别1   组别2   组别3
机器1         1            5         9
机器2         2            9         5
机器3         3            7         6
机器4         4            2         4
机器5         5            1         4
机器6         6            3         5--测试环境
declare @t table(zb varchar(10),机器1 int,机器2 int,机器3 int,机器4 int,机器5 int,机器6 int)
insert into @t select '组别1',1,2,3,4,5,6
union all select '组别2',5,9,7,2,1,3
union all select '组别3',9,5,6,4,4,3SELECT * FROM
(
SELECT  Employee,zb,Orders
FROM
   (SELECT zb, 机器1, 机器2, 机器3, 机器4, 机器5, 机器6
   FROM @t) p
UNPIVOT
   (Orders FOR Employee IN
      (机器1, 机器2, 机器3, 机器4, 机器5, 机器6)
)AS unpvt
) T
PIVOT
(MAX(ORDERS)
for zb in ([组别1],[组别2],[组别3])
)as pt
--结果
/*
Employee   组别1         组别2         组别3
---------- ----------- ----------- -----------
机器1     1           5           9
机器2     2           9           5
机器3     3           7           6
机器4     4           2           4
机器5     5           1           4
机器6     6           3           3
*/
(6 行受影响)--2、ms sql2005 top(@n)declare @n int
set @n=10
SELECT top(@n) percent [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]
      ,[RequiredDate]
      ,[ShippedDate]
      ,[ShipVia]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
SELECT top(@n) [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
from [Orders]update top(@n) [Orders] set [Freight]=[Freight]+10SELECT top(@n) [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[Freight]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipRegion]
      ,[ShipPostalCode]
      ,[ShipCountry]
from [Orders]--3、ms sql2005 try... catch ...set xact_abort on
begin try
   select 1/0
end try
begin catch
select Error_number() as ErrorNumber,Error_Message() as ErrorMessage
end catch--4、ms sql2005 With as 公用表表达式with [Orders_CTE](orderid,[Row Number]) as
(
SELECT orderid
      ,Row_Number() over (order by [EmployeeID]) as [Row Number]
  FROM [Northwind].[dbo].[Orders]
)select * from [Orders_CTE]
--递归create table Dept(
DepartNO  varchar(10),
   DepartName  varchar(20),
TopNo    varchar(10))delete from deptinsert into Dept values('001','董事会','0')
insert into Dept values('002','总裁办公室','001')
insert into Dept values('003','财务部','001')
insert into Dept values('004','市场部','002')
insert into Dept values('005','公关部','002')
insert into Dept values('006','销售部','002')
insert into Dept values('007','分销处','006')
insert into Dept values('008','业务拓展处','004')
insert into Dept values('009','销售科','007')
go
select *
from dept
gowith Dept_CTE as
(
select DepartNO,
   DepartName,
TopNo from Dept
where DepartNo='002'
union all
select child.DepartNO,
   child.DepartName,
child.TopNo from Dept_CTE as Parent
join Dept as child
on parent.DepartNO=child.TopNo
) select * from Dept_CTE--5、ms sql2005 Row_Number Rank Ntile
SELECT [EmployeeID]
      ,Row_Number() over (order by [EmployeeID]) as 'Row Number'
      ,Rank() over (order by [EmployeeID]) as 'Rank'
      ,Dense_Rank() over (order by [EmployeeID]) as 'Dense Rank'
      ,Ntile(6) over (order by [EmployeeID]) as 'Ntile'
  FROM [Northwind].[dbo].[Orders]
order by [EmployeeID]--6、ms sql2005 output
update [Northwind].[dbo].[Employees] set country='test1'
  output deleted.*
  --output inserted.*
  where lastname='Davolio'--7、ms sql2005 cross apply--drop function FunOrdergoCreate Function FunOrder(@EmployeeID int,@n int)
returns table as
return
SELECT top(@n) [OrderID],EmployeeID
FROM Orders
where EmployeeID=@EmployeeID
and EmployeeID<>4
go SELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
join FunOrder(1,3) b
on a.[EmployeeID]=b.EmployeeIDgoSELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
cross apply FunOrder(a.EmployeeID,3) bgoSELECT a.[EmployeeID]
      ,[LastName]
      ,[FirstName],b.[OrderID]
FROM Employees a
outer apply FunOrder(a.EmployeeID,3) b

/*
标题：按某字段合并字符串之一(简单合并)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-06
地点：广东深圳描述：将如下形式的数据按id字段合并value字段。
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     value
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
  return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    if @id = @id_old
       select @s = @s + ',' + cast(@value as varchar)
    else
      begin
        insert @t values(@id_old , stuff(@s,1,1,''))
        select @s = ',' + cast(@value as varchar) , @id_old = @id
      end
    fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
想变成(得到如下结果)：
姓名语文数学物理
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 静态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程   分数
---- ------ ------
李四语文   74.00
李四数学   84.00
李四物理   94.00
李四平均分 84.00
李四总分   252.00
张三语文   74.00
张三数学   83.00
张三物理   93.00
张三平均分 83.33
张三总分   250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number)
from tb a join master..spt_values  b
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用xml完成
SELECT A.id, B.value FROM
(
  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
) B--方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

我希望能在一个sql里定义局部函数，执行完就消失、不会在全局函数里出现的那种。。

调试易

mssql2005 新增的T-SQL语句，最好有详细例子

解决方案 »