Sql-Server-2008-R2

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

  • May 8, 2020

合併查詢結果的 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來自BC不來自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