以下查詢沒有問題
SELECT [TblA].fieldA,
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<1,[fieldA],Left([fieldA],InStr([fieldA],",")-1))) AS a,
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<1,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=1,Right([fieldA],len([fieldA])-len([a])-1), Mid([fieldA],Len([a]) 2,Instr(Len([a]) 2,[fieldA],",")-Len([a])-2)))) AS b,
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<2,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=2,Right([fieldA],len([fieldA])-len([a])-len([b])-2), Mid([fieldA],Len([a]) Len([b]) 3,Instr(Len([a]) Len([b]) 3,[fieldA],",")-Len([a])-Len([b])-3)))) AS c,
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<3,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=3,Right([fieldA],len([fieldA])-len([a])-len([b])-len([c])-3), ""))) AS d
FROM TblA;
但是，當我嘗試在 Union Query 中執行相同操作時，它不起作用（Enter Parameter Valuefor a）
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<1,[fldA],Left([fldA],InStr([fldA],",")-1))) AS a FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<1,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=1,Right([fldA],len([fldA])-len([a])-1),Mid([fldA],Len([a]) 2,Instr(Len([a]) 2,[fldA],",")-Len([a])-2)))) AS b FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<2,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=2,Right([fldA],len([fldA])-len([a])-len([b])-2),Mid([fldA],Len([a]) Len([b]) 3,Instr(Len([a]) Len([b]) 3,[fldA],",")-Len([a])-Len([b])-3)))) AS c FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<3,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=3,Right([fldA],len([fldA])-len([a])-len([b])-len([c])-3), ""))) As d FROM tblA
我的問題是：
有沒有辦法在聯合查詢中使用別名（例如As a）
uj5u.com熱心網友回復：
無法從另一個 SELECT 中的一個 SELECT 參考欄位（別名或非別名）。這些不是嵌套陳述句。此外，只有第一個 SELECT 定義欄位名稱。隨后的 AS 子句將被忽略并且可以洗掉。
構建發生這些計算的第一個查詢物件。然后使用第一個查詢作為源構建 UNION。
SELECT a AS Data, "A" AS Category FROM query
UNION SELECT b, "B" FROM query
UNION SELECT c, "C" FROM query
UNION SELECT d, "D" FROM query;
UNION 不允許重復行。使用 UNION ALL 包括所有記錄，即使有重復。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/453213.html
下一篇：如何將多個欄位查詢為一個