資料庫黑洞

 

 

 

 

本篇要來介紹一般表示式 CTE 的使用時機

在 資料操作技巧: 一般資料表運算式 這篇文章中，有個很棒的詮釋：

 

首先我們先建一個資料庫 ExampleCTE ，裡面放置鄉鎮、縣市和客戶資料表：

-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-01
-- Update date: 2014-06-01
-- Description:	建一個資料庫 ExampleCTE ，裡面放置鄉鎮、縣市和客戶資料表
-- Require: none
-- =============================================
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ExampleCTE')
	ALTER DATABASE ExampleCTE SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--先將資料庫切換到非要卸離的資料庫
USE master
GO
--刪除範例資料庫(ExampleCTE)
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ExampleCTE')
	DROP DATABASE ExampleCTE;
GO
--建立範例資料庫(ExampleCTE)
CREATE DATABASE ExampleCTE;
GO

--切換到範例資料庫
USE ExampleCTE;
GO

--建立縣市
CREATE TABLE CountyCity
(
	CountyCityID INT NOT NULL,
	CountyCityName NVARCHAR(20) NOT NULL
);

--建立鄉鎮
CREATE TABLE Town
(
	TownID INT NOT NULL IDENTITY(1,1),
	CountyCityID INT NOT NULL,
	ZipCode SMALLINT NOT NULL,
	TownName NVARCHAR(20) NOT NULL
);

--建立客戶
CREATE TABLE Customers
(
	CustomerName NVARCHAR(20) NOT NULL,
	ZipCode1 SMALLINT NULL,
	Address1 NVARCHAR(20) NULL,
	ZipCode2 SMALLINT NULL,
	Address2 NVARCHAR(20) NULL
);

--寫入縣市資料
INSERT INTO CountyCity (CountyCityID, CountyCityName) 
VALUES
	('1', '台北市');

--寫入鄉鎮資料
INSERT INTO Town (CountyCityID, ZipCode, TownName)
VALUES
	('1', '100','中正區'),
	('1', '103','大同區'),
	('1', '104','中山區');

--寫入客戶資料
INSERT INTO Customers (CustomerName, ZipCode1, Address1, ZipCode2, Address2)
VALUES
	('小明','100','長安路555號', NULL, NULL),
	('小華','100','長安路99號','103','長安路75號'),
	('小祥', NULL, NULL,'104','長安路23號');
GO

 

 第一種方式，對於常用的資料，我們會建立檢視表方便日後查詢：

-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-01
-- Update date: 2014-06-01
-- Description:	1.檢視表查詢
-- Require: none
-- =============================================
IF OBJECT_ID('ZipCode', 'V') IS NOT NULL
	DROP VIEW ZipCode;
GO

--建立檢視表
CREATE VIEW ZipCode
AS
	SELECT B.ZipCode, A.CountyCityName, B.TownName
	FROM CountyCity A
	INNER JOIN Town B ON A.CountyCityID = B.CountyCityID
GO

--查詢客戶完整地址
SELECT A.CustomerName, A.ZipCode1, B.CountyCityName + B.TownName + A.Address1 AS TotalAddress1
	, A.ZipCode2, C.CountyCityName + C.TownName + A.Address2 AS TotalAddress2
FROM Customers A
LEFT JOIN ZipCode B ON A.ZipCode1 = B.ZipCode
LEFT JOIN ZipCode C ON A.ZipCode2 = C.ZipCode

 

第二種方式，如果沒有檢視表，我們會下語法作衍生資料表(內嵌檢視)查詢：

-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-01
-- Update date: 2014-06-01
-- Description:	2.延伸資料表查詢
-- Require: none
-- =============================================
SELECT A.CustomerName, A.ZipCode1, B.CountyCityName + B.TownName + A.Address1 AS TotalAddress1
	, A.ZipCode2, C.CountyCityName + C.TownName + A.Address2 AS TotalAddress2
FROM Customers A
LEFT JOIN (
	SELECT B.ZipCode, A.CountyCityName, B.TownName
	FROM CountyCity A
	INNER JOIN Town B ON A.CountyCityID = B.CountyCityID
) B ON A.ZipCode1 = B.ZipCode
LEFT JOIN (
	SELECT B.ZipCode, A.CountyCityName, B.TownName
	FROM CountyCity A
	INNER JOIN Town B ON A.CountyCityID = B.CountyCityID
) C ON A.ZipCode2 = C.ZipCode

 

第三種方式，在2005+的版本，支援使用 CTE 查詢，邏輯比延伸資料表更分明：

-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-01
-- Update date: 2014-06-01
-- Description:	3.CTE查詢
-- Require: none
-- =============================================
;WITH ZipCode AS (
	SELECT B.ZipCode, A.CountyCityName, B.TownName
	FROM CountyCity A
	INNER JOIN Town B ON A.CountyCityID = B.CountyCityID
)
SELECT A.CustomerName, A.ZipCode1, B.CountyCityName + B.TownName + A.Address1 AS TotalAddress1
	, A.ZipCode2, C.CountyCityName + C.TownName + A.Address2 AS TotalAddress2
FROM Customers A
LEFT JOIN ZipCode B ON A.ZipCode1 = B.ZipCode
LEFT JOIN ZipCode C ON A.ZipCode2 = C.ZipCode

 

參考資料：

WITH common_table_expression (Transact-SQL)

資料操作技巧: 一般資料表運算式

嘗試卸載資料庫時，發生資料庫正在使用的而無法卸載的可能解決方案

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~