關於 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
)。)現在,您說當您使用 a
UNION
時會出現轉換錯誤,因為在一個表中列 C 是 avarchar
而在另一個表中是 asmalldatetime
。您的選擇是:
- 解決這個問題。如果您認為這些列相同且兼容,則它們應該具有相同的數據類型並包含相似的數據。這意味著在刪除任何無法直接轉換為
smalldatetime
.- 如果該
varchar
列需要具有與 不兼容的不同數據smalldatetime
,那麼最好在UNION
查詢中將其呈現為不同的輸出列。- 之後,您可以對
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;