從聯合查詢中獲取表名？

這是我的查詢

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1

它工作正常。

productName現在我想在將來獲取各自的表名。

1. 那麼我該如何獲取呢？

我試過AS..像這樣：

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;

但沒有得到輸出。

2. 我該如何更正查詢並提高查詢性能？

通常在使用UNION並且您需要知道特定行來自哪個表時，您將使用儲存在類似於以下列的硬編碼值：

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;

這將返回一個Source帶有標識符的新列，該標識符顯示該行來自哪個表。

正如@ypercube 在評論中建議的那樣，您可能還想考慮將其更改為 a UNION ALL- 這將包括重複，但您還將擁有它來自哪個表的標識符。使用 aUNION ALL將消除刪除重複數據對性能的影響。

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;

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