select (sql server )

-- 1-1 基本的 SELECT 敘述

SELECT select_list
FROM table_list

-- 從每一行選出特定的欄位

USE AdventureWorks
GO
SELECT ContactID,
Title,
FirstName,
LastName
FROM Person.Contact

-- 為每一列選出所有欄位

USE AdventureWorks
GO
SELECT *
FROM Person.Contact

--用 WHERE 子句指定從查詢結果傳回的行列

SELECT Title,
FirstName,
LastName
FROM Person.Contact
WHERE Title = s.

-- 否決搜尋條件

SELECT Title,
FirstName,
LastName
FROM Person.Contact
WHERE NOT Title = 'Ms.'

--組合搜尋條件

SELECT Title,
FirstName,
LastName
FROM Person.Contact
WHERE Title = 'Ms.' AND
LastName = 'Antrim'

SELECT Title,
FirstName,
LastName
FROM Person.Contact
WHERE Title = 'Ms.' OR
LastName = 'Antrim'

-- 如何讓 WHERE 子句語意不模糊

SELECT Title,
FirstName,
LastName
FROM Person.Contact
WHERE Title = 'Ms.' AND
FirstName = 'Catherine' OR
LastName = 'Adams'

SELECT ContactID,
Title,
FirstName,
MiddleName,
LastName
FROM Person.Contact
WHERE (Title = 'Ms.' AND
FirstName = 'Catherine') OR
LastName = 'Adams'

-- 使用 BETWEEN 搜尋特定範圍資料

SELECT SalesOrderID,
ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '7/28/2002' AND '7/29/2002'

-- 比較功能的用法

SELECT ProductID,
Name,
StandardCost
FROM Production.Product
WHERE StandardCost < 110.0000

--檢查 NULL 值

SELECT ProductID,
Name,
Weight
FROM Production.Product
WHERE Weight IS NULL

--根據數值清單傳回資料列

SELECT ProductID,
Name,
Color
FROM Production.Product
WHERE Color IN ('Silver', 'Black', 'Red')

--LIKE 與萬用字元的用法

SELECT ProductID,
Name
FROM Production.Product
WHERE Name LIKE 'B%'

SELECT ProductID,
Name
FROM Production.Product
WHERE Name LIKE '%/_%' ESCAPE '/'

--ORDER BY 子句的用法

SELECT p.Name,
h.EndDate,
h.ListPrice
FROM Production.Product p
INNER JOIN Production.ProductListPriceHistory h ON
p.ProductID = h.ProductID
ORDER BY p.Name, h.EndDate

SELECT p.Name,
h.EndDate,
h.ListPrice
FROM Production.Product p
INNER JOIN Production.ProductListPriceHistory h ON
p.ProductID = h.ProductID
ORDER BY p.Name DESC, h.EndDate DESC

SELECT p.Name
FROM Production.Product p
ORDER BY p.Color

-- 使用關鍵字 TOP 配合排序查詢結果

SELECT TOP 10 v.Name,
v.CreditRating
FROM Purchasing.Vendor v
ORDER BY v.CreditRating DESC, v.Name

DECLARE @Percentage float
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name

-- GROUP BY 子句的用法

SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY OrderDate

-- GROUP BY ALL 的用法

SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY ALL OrderDate

-- 使用 HAVING 選擇性地查詢同類資料

SELECT s.Name,
COUNT(w.WorkOrderID) Cnt
FROM Production.ScrapReason s
INNER JOIN Production.WorkOrder w ON
s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING COUNT(*)>50

-- 使用 DISTINCT 移除重複數值

SELECT DISTINCT HireDate
FROM HumanResources.Employee

-- 在聚合函數裡使用 DISTINCT

SELECT AVG(ListPrice)
FROM Production.Product

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product

-- 欄位別名的用法

SELECT Color AS 'Grouped Color',
AVG(DISTINCT ListPrice) AS 'Average Distinct List Price',
AVG(ListPrice) 'Average List Price'
FROM Production.Product
GROUP BY Color

-- 用 SELECT 來製作指令稿

SELECT column_name + ' IS NULL AND '
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'Employee'
ORDER BY ORDINAL_POSITION

-- 組合字串

SELECT 'The ' +
p.name +
' is only ' +
CONVERT(varchar(25),p.ListPrice) +
'!'
FROM Production.Product p
WHERE p.ListPrice between 100 AND 120
ORDER BY p.ListPrice

-- 用 SELECT 產生以逗點區隔的清單

DECLARE @Shifts varchar(20)
SET @Shifts = ''
SELECT @Shifts = @Shifts + s.Name + ','
FROM HumanResources.Shift s
ORDER BY s.EndTime
SELECT @Shifts

-- INTO 子句的用法

SELECT CustomerID,
Name,
SalesPersonID,
Demographics
INTO Store_Archive
FROM Sales.Store

SELECT CustomerID,
Name,
SalesPersonID,
Demographics
INTO Store_Archive
FROM Sales.Store
WHERE 1=0

-- 使用子查詢來檢查是否符合

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000 AND
SalesOrderID = s.SalesOrderID)

SELECT SalesPersonID,
SalesQuota CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota IN
(SELECT MAX(SalesQuota)
FROM Sales.SalesPerson)

-- INNER 聯結的用法

SELECT p.Name,
s.DiscountPct
FROM Sales.SpecialOffer s
INNER JOIN Sales.SpecialOfferProduct o ON
s.SpecialOfferID = o.SpecialOfferID
INNER JOIN Production.Product p ON
o.ProductID = p.ProductID
WHERE p.Name = 'All-Purpose Bike Stand'

-- OUTER 聯結的用法

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
INNER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID = t.StateProvinceID

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID = t.StateProvinceID

-- 交叉(CROSS)聯結的用法

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
CROSS JOIN Sales.SalesTaxRate t

-- 執行自體聯結

SELECT e.EmployeeID,
e.Title,
m.Title AS ManagerTitle
FROM HumanResources.Employee e
LEFT OUTER JOIN HumanResources.Employee m ON
e.ManagerID = m.EmployeeID

--衍生(derived)資料表的使用

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
s.SalesOrderID = d.SalesOrderID

-- 使用聯集(UNION)組合查詢結果

SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION
SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY SalesPersonID DESC, QuotaDate DESC

-- CROSS APPLY 的用法

CREATE FUNCTION dbo.fn_WorkOrderRouting
(@WorkOrderID int) RETURNS TABLE
AS
RETURN
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID
GO

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
ORDER BY w.WorkOrderID,
w.OrderQty,
r.ProductID

--OUTER APPLY 的用法

INSERT INTO [AdventureWorks].[Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(1,
1,
1,
GETDATE(),
GETDATE(),
GETDATE(),
1,
GETDATE())

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder AS w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder AS w
OUTER APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)

-- 以 TABLESAMPLE 隨機傳回資料列

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

--以 PIVOT 將單一欄位值轉換為多重欄位與彙總資料
Columns and Aggregate Data

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

-- 以 EXCEPT 與 INTERSECT 傳回獨特或是符合的資料列

-- First two new tables based on ProductionProduct will be
-- created, in order to demonstrate EXCEPT and INTERSECT.
-- See Chapter 8for more on ROW_NUMBER
-- Create TableA
SELECT prod.ProductID,
prod.Name
INTO dbo.TableA
FROM
(SELECT ProductID,
Name,
ROW_NUMBER() OVER (ORDER BY ProductID) RowNum
FROM Production.Product) prod
WHERE RowNum BETWEEN 1 and 20
-- Create TableB
SELECT prod.ProductID,
prod.Name
INTO dbo.TableB
FROM
(SELECT ProductID,
Name,
ROW_NUMBER() OVER (ORDER BY ProductID) RowNum
FROM Production.Product) prod
WHERE RowNum BETWEEN 10 and 29

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

SELECT ProductID,
Name
FROM TableA
INTERSECT
SELECT ProductID,
Name
FROM TableB

--以 WITH CUBE 做資料加總

SELECT i.Shelf,
SUM(i.Quantity) Total
FROM Production.ProductInventory i
GROUP BY i.Shelf
WITH CUBE

--結合 GROUPING 與 WITH CUBE

SELECT i.Shelf,
GROUPING(i.Shelf) Source,
SUM(i.Quantity) Total
FROM Production.ProductInventory i
GROUP BY i.Shelf
WITH CUBE

--以 WITH ROLLUP 做資料加總

SELECT i.Shelf,
p.Name,
SUM(i.Quantity) Total
FROM Production.ProductInventory i
INNER JOIN Production.Product p ON
i.ProductID = p.ProductID
GROUP BY i.Shelf, p.Name
WITH ROLLUP

-- 聯結提示(Join Hints)的運用

-- (More on SHOWPLAN_TEXT in Chapter 28
SET SHOWPLAN_TEXT ON
GO
SELECT p.Name,
r.ReviewerName,
r.Rating
FROM Production.Product p
INNER JOIN Production.ProductReview r ON
r.ProductID = p.ProductID
GO
SET SHOWPLAN_TEXT OFF
GO

SET SHOWPLAN_TEXT ON
GO
SELECT p.Name,
r.ReviewerName,
r.Rating
FROM Production.Product p
INNER HASH JOIN Production.ProductReview r ON
r.ProductID = p.ProductID
GO
SET SHOWPLAN_TEXT OFF
GO

-- 查詢提示(Query Hints)的運用

SELECT SalesOrderID,
ProductID,
UnitPrice,
OrderQty
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = '5CE9-4D75-8F'
ORDER BY SalesOrderID,
ProductID
OPTION (RECOMPILE)

-- 資料表提示(Table Hints)的應用

SELECT DocumentID,
Title
FROM Production.Document
WITH (NOLOCK)
WHERE Status = 1

-- 非遞迴式 CTE 的運用

WITH VendorSearch (RowNumber, VendorName, AccountNumber)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Name) RowNum,
Name,
AccountNumber
FROM Purchasing.Vendor
)
SELECT RowNumber,
VendorName,
AccountNumber
FROM VendorSearch
WHERE RowNumber BETWEEN 1 AND 5
UNION
SELECT RowNumber,
VendorName,
AccountNumber
FROM VendorSearch
WHERE RowNumber BETWEEN 100 AND 104

WITH VendorSearch (VendorID, VendorName)
AS
(
SELECT VendorID,
Name
FROM Purchasing.Vendor
)
SELECT v.VendorID,
v.VendorName,
p.ProductID,
p.StandardPrice
FROM VendorSearch v
INNER JOIN Purchasing.ProductVendor p ON
v.VendorID = p.VendorID
ORDER BY v.VendorName

--遞迴式 CTE 的運用

CREATE TABLE dbo.Company
(CompanyID int NOT NULL PRIMARY KEY,
ParentCompanyID int NULL,
CompanyName varchar(25) NOT NULL)

INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (1, NULL, 'Mega-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (2, 1, 'Mediamus-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (3, 1, 'KindaBigus-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (4, 3, 'GettinSmaller-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (5, 4, 'Smallest-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (6, 5, 'Puny-Corp')
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES (7, 5, 'Small2-Corp')

WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel)
AS
(
SELECT ParentCompanyID,
CompanyID,
CompanyName,
0 AS CompanyLevel
FROM dbo.Company
WHERE ParentCompanyID IS NULL
UNION ALL
SELECT c.ParentCompanyID,
c.CompanyID,
c.CompanyName,
p.CompanyLevel + 1
FROM dbo.Company c
INNER JOIN CompanyTree p
ON c.ParentCompanyID = p.CompanyID
)
SELECT ParentCompanyID, CompanyID, CompanyName, CompanyLevel
FROM CompanyTree