MS-SQL2005新特性(Common Table Expression)

1.行变列
  PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为行值。功能等同于select case语句。

SELECT
   PurchaseOrderID
  ,EmployeeID
  ,VendorID
FROM
  PurchaseOrderHeader

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
-----------------------------------------------
VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5
-----------------------------------------------


UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。假设以上示例中生成的表在数据库中存储为 
pvt，并且您需要将列标识符 
Emp1、Emp2、Emp3、Emp4 和 
Emp5 
转换为对应于某个特定供应商的行值。这意味着必须标识另外两个列。包含所转换列值（Emp1、Emp2...）的列将被称为 
Employee，保存当前驻留在所转换列下的值的列将被称为 Orders。这些列分别对应于 
Transact-SQL 定义中的 pivot_column 和 value_column。该查询如下所示：

--Create the table and insert values as portrayed in the above example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
-----------------------------------------------
VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5
...
-----------------------------------------------

2.递归

create table t(id int,pid int);

insert into t select 1,0

union select 2,1

union select 3,1

union select 4,2

union select 5,1

union select 6,4

declare

@liv_id int;

begin

with temp_tb as(

select t1.id from t t1 where t1.id= liv_id union all

select t2.id from t t2 inner join temp_tb t3 on t2.pid=t3.id

)

select * from temp_tb OPTION (MAXRECURSION 3);

end;

3.分页

select * from(select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders) a
where row between 20 and 30

4.排名

select * from(select OrderId, Freight, RANK() OVER(order by Freight) as rank from Orders) a
where rank between 20 and 30