關於 UNION 運算符及其隱式數據類型轉換的問題

合併查詢結果的 UNION 運算符的數量是否有限制？

我有一個奇怪的情況，我有一個名為 TransmittalSheet TS 的表，它與 2 個表中的每一個都具有雙重（1 到多）關係：（DocumentInfo 和 EquipmentJobPlan 表），它們都具有幾乎完全相同的列名和數據類型。來自上面的父表 TS 的每個主鍵 (TransmittalID) 在上面的每個子表中都有兩個對應的外鍵。FK 對列稱為：（Docs_Implementing、Docs_Obsolescing）和（JobPlan_TS_Implementing 和 JobPlan_TS_Obsolescing）。

我需要查詢每個 Transmittal 是否有任何文件或設備JobPlans 與之關聯（每個表關聯的 Imp 或 Obs），所以我相信我需要有 3 個這樣的 UNION 運算符：

Select A, B, C , ‘Imp Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS 
ON Di.DocumentTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Imp JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’

有一個更好的方法嗎？使用 SQL Server 2008R2。

此外，Union 似乎正在為列 C 進行一些數據轉換，例如：兩列都在擷取文件版本。它在 DocumentInfo 表中有一個 varchar(75) 數據類型，但在 JobPlan 表中有一個 smallDateTime，我應該進行顯式轉換還是 Union 正在執行的隱式轉換可以？謝謝

一方面，您當然可以簡化查詢，例如（我添加了架構前綴，您應該始終使用）：

Select A, B, C
 From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS 
 ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
 Where TransmittalNumber = 'TS- 0001'
UNION
Select A, B, C
 From dbo.EquipmentJobPlan As EQ dbo.INNER JOIN TransmittalSheet TS
 ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
 Where TransmittalNumber = 'TS- 0001';

或者甚至：

SELECT A, B, C
 FROM dbo.TransmittalSheet TS
 LEFT OUTER JOIN dbo.DocumentInfo As Di 
 ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
 LEFT OUTER JOIN dbo.EquipmentJobPlan As EQ
 ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
WHERE TS.TransmittalNumber = 'TS- 0001' 
AND COALESCE(Di.KeyColumn, EQ.KeyColumn) IS NOT NULL
GROUP BY A, B, C;

（我不知道這是否GROUP BY有必要，因為我不知道您是否UNION故意使用它來刪除重複項。儘管我確實建議在所有列引用上放置表別名，以便讀者知道哪些列來了來自哪些表。您可能需要在COALESCE此處使用並選擇哪個表優先於任何輸出列（如果A來自B或C不來自TransmittalSheet）。）

現在，您說當您使用 aUNION時會出現轉換錯誤，因為在一個表中列 C 是 avarchar而在另一個表中是 a smalldatetime。您的選擇是：

1. 解決這個問題。如果您認為這些列相同且兼容，則它們應該具有相同的數據類型並包含相似的數據。這意味著在刪除任何無法直接轉換為smalldatetime.
2. 如果該varchar列需要具有與 不兼容的不同數據smalldatetime，那麼最好在UNION查詢中將其呈現為不同的輸出列。
3. 之後，您可以對smalldatetime類型應用顯式強制轉換，以便將其轉換為varchar，而不是 SQL Server 將執行此操作的方式（即嘗試將字元串轉換為日期）。如果您只想顯示有效smalldatetime數據，那麼您可以CAST採用另一種方​​式，但使用CASE表達式以便僅在有效數據上嘗試轉換。隱式轉換可以遵循數據類型優先級的規則，但如果varchar列包含任何無法轉換為的值smalldatetime，它會失敗並顯示 Msg 295，正如您所發現的。即使這些行沒有出現在輸出中，也會發生這種情況 - 轉換可以在這些行被過濾掉之前發生（我已經在這裡寫過，這裡，這里和這裡，它被報告為 Connect (RIP) 上的一個錯誤。並且因為您使用UNION而不是UNION ALL，您可能更有可能遇到無效值，因為DISTINCT SORT這（通常）添加的其他操作可能會改變 SQL Server 優化整體計劃的方式。

所以，如果你不能從上面的重寫中受益（或者如果它們由於某種原因產生不同的結果），並且你不能做 1. 或 2.，那麼我會UNION像這樣重寫版本：

-- against the table where C is smalldatetime:
SELECT A, B, CONVERT(CHAR(10), C, 120)
FROM ...

-- or against the table where C is varchar:
SELECT A, B, CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM ...

您可以使用的更本地化的範例：

CREATE TABLE dbo.foo(A INT, B INT, C VARCHAR(75));
CREATE TABLE dbo.bar(A INT, B INT, C SMALLDATETIME);    
INSERT dbo.foo(A,B,C) VALUES(1,2,'three'),(2,4,'20150101');
INSERT dbo.bar(A,B,C) VALUES(1,2,'20050101'),(2,4,'20050101');
GO

-- these both work of course:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1;
SELECT A,B,CONVERT(SMALLDATETIME,C) FROM dbo.foo WHERE ISDATE(C) = 1;
GO

-- fails:
SELECT A,B,C FROM dbo.foo
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds, but may not always:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1
UNION 
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds:
SELECT A, B, C = CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM dbo.foo
UNION 
SELECT A, B, C FROM dbo.bar;

-- succeeds:
SELECT A, B, C FROM dbo.foo
UNION 
SELECT A, B, C = CONVERT(VARCHAR(10), C, 120) FROM dbo.bar;
GO

DROP TABLE dbo.foo, dbo.bar;

最後，請不要CAST( AS Char)沒有長度，或任何其他變數類型。

引用自：https://dba.stackexchange.com/questions/99066