我嘗試了各種方法來在創建視圖時使用動態變數創建視圖。
DECLARE @Test VARCHAR(64)='ABC'; // <==== attempt
SELECT TOP 10
U.ID
,X.NAME AS yyyy
,X.E AS zzzz
FROM TABLE_A U
JOIN TABLE_B X ON U.ID=X.ID
WHERE U.STATUS='HELLO'
**AND X.NAME=@Test**
ORDER BY U.ID DESC
即使我嘗試使用 CTE，我也會收到錯誤訊息
CREATE VIEW TESTVIEW AS
WITH Tests AS (DECLARE @Test VARCHAR(64)='ABC')
SELECT TOP 10
U.ID
,X.NAME AS yyyy
,X.E AS zzzz
FROM TABLE_A U
JOIN TABLE_B X ON U.ID=X.ID
WHERE U.STATUS='HELLO'
**AND X.NAME=Tests**
ORDER BY U.ID DESC
uj5u.com熱心網友回復：
您不能在視圖中宣告變數。為什么不使用子句SELECT中的字串創建視圖然后從視圖中創建？WHERE
CREATE VIEW TESTVIEW AS
SELECT TOP 10 -- Wouldn't recommend hardcoding this in a view
U.ID
,X.NAME AS yyyy
,X.E AS zzzz
,X.NAME
FROM TABLE_A U
JOIN TABLE_B X ON U.ID=X.ID
WHERE U.STATUS='HELLO';
SELECT
*
FROM TESTVIEW
WHERE NAME = 'ABC';
您可以使用 CTE 在視圖中模擬它，例如：
CREATE VIEW TESTVIEW AS
WITH CTE AS (
SELECT
'ABC' as var
)
SELECT TOP 10 --Still wouldn't recommend hardcoding this in a view
U.ID
,X.NAME AS yyyy
,X.E AS zzzz
,X.NAME
FROM TABLE_A U
JOIN TABLE_B X ON U.ID=X.ID
WHERE U.STATUS='HELLO'
AND X.NAME = (SELECT var FROM cte);
SELECT * FROM TESTVIEW;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/456909.html
上一篇：對Employee表使用SQL和Matillion，我只需要提取最近的員工雇用日期
下一篇：僅替換行首