考慮一些代碼，例如
SELECT
CASE
WHEN [DECISION-MAKER] = 'COKE' THEN 'GIVE COKE'
WHEN [DECISION-MAKER] IN ('PEPSI', 'BLOOD') THEN 'GIVE DEATH'
END AS [EMPLOYEE-ASSIGNMENT],
CASE [DECISION-MAKER]
WHEN 'COKE' THEN 'Employee prefers coke. Give coke.'
WHEN 'PEPSI' THEN 'Employee prefers pepsi. Give death.'
WHEN 'BLOOD' THEN 'Employee is some sort of vampire. Give death.'
END AS [ASSIGNMENT-REASON]
FROM
(
SELECT *,
CASE
WHEN [COMPLEX-LOGIC-1] THEN 'COKE'
WHEN [COMPLEX-LOGIC-2] THEN 'PEPSI'
WHEN [COMPLEX-LOGIC-3] THEN 'BLOOD'
END AS [DECISION-MAKER]
FROM [WHEREVER]
)
我相信這種糟糕代碼的技術術語是“字串型別”。上述代碼中的關鍵問題是，決策是基于開發人員需要輸入并始終正確的字串輸出做出的。如果出現任何問題，該語言將無法拋出錯誤。在傳統語言中，解決方法是構建某種字典來處理這些情況。T-SQL 中的慣用解決方案是什么？我不喜歡制作一次性表的想法，但也許臨時表會有所幫助？
uj5u.com熱心網友回復：
我不會擔心有一張“在一個地方使用”的桌子。如果您仍然擔心它會弄亂您的資料庫，您可以使用表變數。
DECLARE @AssignmentAndReason AS TABLE (
DECISION-MAKER varchar,
EMPLOYEE-ASSIGNMENT varchar,
ASSIGNMENT-REASON varchar
);
INSERT INTO @AssignmentAndReason VALUES
('COKE', 'GIVE COKE', 'Employee prefers coke. Give coke.'),
('PEPSI', 'GIVE DEATH', 'Employee prefers pepsi. Give death.'),
('BLOOD', 'GIVE DEATH', 'Employee is some sort of vampire. Give death.');
SELECT [EMPLOYEE-ASSIGNMENT], [ASSIGNMENT-REASON],
FROM [WHEREVER]
JOIN @AssignmentAndReason ON [DECISION-MAKER] = CASE
WHEN [COMPLEX-LOGIC-1] THEN 'COKE'
WHEN [COMPLEX-LOGIC-2] THEN 'PEPSI'
WHEN [COMPLEX-LOGIC-3] THEN 'BLOOD'
END
uj5u.com熱心網友回復：
使用“與 as（查詢）”
with firstLogic as
(
select PersonID, Name,
CASE
WHEN valueDRINK=1 or valueDRINK=2 THEN 'COKE'
WHEN valueDRINK=3 or valueDRINK=4 THEN 'PEPSI'
WHEN valueDRINK=5 THEN 'BLOOD'
END AS [DECISION-MAKER]
from Persons
where country='co'
)
select PersonID, Name ,[DECISION-MAKER],
CASE
WHEN [DECISION-MAKER] = 'COKE' THEN 'GIVE COKE'
WHEN [DECISION-MAKER] IN ('PEPSI', 'BLOOD') THEN 'GIVE DEATH'
END AS [EMPLOYEE-ASSIGNMENT],
CASE [DECISION-MAKER]
WHEN 'COKE' THEN 'Employee prefers coke. Give coke.'
WHEN 'PEPSI' THEN 'Employee prefers pepsi. Give death.'
WHEN 'BLOOD' THEN 'Employee is some sort of vampire. Give death.'
END AS [ASSIGNMENT-REASON]
from firstLogic
"with" 允許你有不同的查詢，然后加入它們而不創建臨時表
你可以在這個例子中看到它
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/460917.html