一.隨機查詢表

SELECT FirstName,LastName
FROM Person.Contact TABLESAMPLE SYSTEM
(2 PERCENT)

二.PIVOT軸查詢應用(可以方便的轉換不含主索引鍵資料表的格式)

SELECT s.Name ShiftName,
h.EmployeeID,
d.Name DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d ON
h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s ON
h.ShiftID = s.ShiftID
WHERE EndDate IS NULL AND
d.Name IN ('Production', 'Engineering', 'Marketing')
ORDER BY ShiftName

-------------------------------------------------
SELECT ShiftName,
Production,
Engineering,
Marketing
FROM
(SELECT s.Name ShiftName,
h.EmployeeID,
d.Name DepartmentName
FROM HumanResources.EmployeeDepartmentHistory h
INNER JOIN HumanResources.Department d ON
h.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Shift s ON
h.ShiftID = s.ShiftID
WHERE EndDate IS NULL AND
d.Name IN ('Production', 'Engineering', 'Marketing')) AS a
PIVOT
(
COUNT(EmployeeID)
FOR DepartmentName IN ([Production], [Engineering], [Marketing]))
AS b
ORDER BY ShiftName

三.UNPIVOT

CREATE TABLE dbo.Contact
(EmployeeID int NOT NULL,
PhoneNumber1 bigint,
PhoneNumber2 bigint,
PhoneNumber3 bigint)
GO
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 1, 2718353881, 3385531980, 5324571342)
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 2, 6007163571, 6875099415, 7756620787)
INSERT dbo.Contact
(EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
VALUES( 3, 9439250939, NULL, NULL)

SELECT EmployeeID,
PhoneType,
PhoneValue
FROM
(SELECT EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3
FROM dbo.Contact) c
UNPIVOT
(PhoneValue FOR PhoneType IN ([PhoneNumber1], [PhoneNumber2], [PhoneNumber3])
) AS p

四.交叉與排除

SELECT ProductID,
Name
FROM TableA
EXCEPT
SELECT ProductID,
Name
FROM TableB
-----------------
SELECT ProductID,
Name
FROM TableA a

where exists(
SELECT ProductID,
Name
FROM TableB where a.ProductID=TableB.ProductID)
-------------等價
SELECT ProductID,
Name
FROM TableA
INTERSECT
SELECT ProductID,
Name
FROM TableB