#副標= SQL Server 2005練功房（1）
#大標=SQL Server 2005的T-SQL新功能
#作者=文／楊志強
#引言=介紹SQL Server 2005的語法新功能與強化的部分，讓讀者能以最快的速度上手新的功能。
#內文=



============程式===========
程式1
--建立員工資料檔並使用自我參照的Foreign Key
USE tempdb; DROP table Employees;
CREATE TABLE Employees
(empid   int not null primary key, 
 empname varchar(30)    , 
 mgrid   int references Employees(empid));
GO
--新增資料，ADA與JUDY都是回報給LEWIS
INSERT INTO Employees(empid, empname, mgrid) 
VALUES(1, 'LEWIS', null)
INSERT INTO Employees(empid, empname, mgrid) 
VALUES(2, 'ADA' , 1)
INSERT INTO Employees(empid, empname, mgrid) 
VALUES(3, 'JUDY', 1)
SELECT * FROM Employees
--按下F5查詢資料如圖1
============程式===========


============程式===========
程式2
BEGIN TRY    --使用新的錯誤處理機制
  BEGIN TRAN 
    UPDATE Employees SET mgrid=2 WHERE 
empid=3
    PRINT ' 完成 主管更換 作業 '  
-- 此處陳述式可以成功
INSERT INTO Employees(empid, empname, 
mgrid) 
VALUES(4, 'Julia',5 )          
-- 此處將發生參閱失敗
    print ' 完成 員工新增 作業 '
    COMMIT TRAN 
    PRINT ' 兩筆同時交易完成 '
END TRY
BEGIN CATCH
  PRINT '錯誤代碼 : ' + 
CAST(ERROR_NUMBER() AS VARCHAR(10)) 
+'  '+ ERROR_MESSAGE()
 IF (XACT_STATE()) = -1
  BEGIN
 PRINT '交易雖已開啟但是不可以進行COMMIT,
僅可ROLLBACK'
 ROLLBACK TRANSACTION
  END
 ELSE IF (XACT_STATE()) = 1
  BEGIN     
 PRINT '交易已開啟,可以進行COMMIT,也可
ROLLBACK'     
 COMMIT TRANSACTION  
--注意此處確認僅確認主管更換作業 
 PRINT '完成 Catch Block COMMIT'
  END
 ELSE
  BEGIN
     PRINT '無交易可以處理'
  END
END CATCH --按下F5執行結果如圖2
============程式===========


============程式===========
程式3
WITH TopPeople (MgrID , People)
AS ( SELECT  ManagerID,count(*)
  FROM   HumanResources.Employee 
--使用範例資料庫員工資料
  GROUP BY ManagerID )
 SELECT * FROM TopPeople WHERE People >=10
--按下F5查詢資料如圖4
============程式===========



============程式===========
程式4
CREATE TABLE Employees
( empid int NOT NULL, --員工編號
mgrid int NULL, --主管編號
 empname varchar(25) NOT NULL, --員工姓名
 salary money NOT NULL, --薪資
  CONSTRAINT PK_Employees PRIMARY
KEY(empid),
  CONSTRAINT FK_Employees_mgrid_empid
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid)
--主管編號必須存在於員工編號
)   --結果如圖6
============程式===========


============程式===========
程式5
WITH EmpCTE(empid, empname, mgrid, lvl,sort,Salary)
AS
(  -- Anchor Member 
  SELECT empid, empname, mgrid, 0,
cast(empid as varbinary(max)),Salary
  FROM Employees
  WHERE empid = 1 --起始節點
  UNION ALL  
  -- Recursive Member 
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
Sort+cast(e.empid as varbinary(max)),e.Salary
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid )
SELECT REPLICATE('_',LVL*2) +EMPNAME +
'('+CONVERT(VARCHAR(30),EMPID)+')' as '員工
階層',
MGRID as '主管編號',LVL as '階層'
FROM   EmpCTE
Order By Sort
============程式===========


============程式===========
程式6
SELECT * FROM SftSales 
PIVOT(SUM(Sales) FOR Quarter IN ([Q1],[Q2])) t
--按下F5，輸出結果圖8
============程式===========



============程式===========
程式7
CREATE TABLE OpenSchema
( objectid  INT          NOT NULL,
  attribute NVARCHAR(30) NOT NULL,
  value     SQL_VARIANT  NOT NULL, 
  PRIMARY KEY (objectid, attribute) )
SELECT * FROM OpenSchema
PIVOT (MAX(value) for attribute in 
([屬性1],[屬性2],[屬性3],[屬性4],[屬性5])) as pvt
============程式===========



============程式===========
程式8
SELECT orderid, customerid, 
  ROW_NUMBER() OVER(ORDER BY customerid) 
AS '序號',
  RANK() OVER(ORDER BY customerid) 
AS '等級相同跳號',
  DENSE_RANK() OVER(ORDER BY customerid)
AS '等級相同續號',
  NTILE(5) OVER(ORDER BY customerid) 
AS '區分成5 群組'
FROM  orders
WHERE orderid < 10400 AND customerid <= 'BN'
--輸出結果如圖10
============程式===========