MSSQL - 特殊字符语法 [英] MSSQL - Special character syntax

问题描述

您好，我是MSSQL的新手。我有一个查询，我创建了2个CTE。然后使用第一个内连接更新第二个。这似乎一切正常，当我想运行我的最终选择时，问题就出现了，因为SQL Server Management工作室不会识别我以前的CTE，建议我的语法不正确。



我可以发布所有内容，但简单地说是：



; WITH CTE1 AS（很多代码），

CTE2 AS（很多代码）

UPDATE CTE2一些代码;



SELECT



在SELECT中，CTE不是一个选项。在CTE2 AS（）之后是否应该有一些其他特殊字符或UPDATE语句之后有什么不同？

Hello, I'm fairly new to MSSQL. I have a query where I create 2 CTEs. The second one is then updated using an inner join to the first. This seems to be all working, the problem then occurs when I want to run my final select as the SQL Server Management studio wont' recognize my previous CTE's, suggesting I have the syntax incorrect.

I can post it all, but briefly it is:

;WITH CTE1 AS (Lots of code),
CTE2 AS (Lots of Code)
UPDATE CTE2 some code;

SELECT

At the SELECT the CTEs aren't an option. Should there be some other special character after the CTE2 AS () or something different after the UPDATE statement?

推荐答案

最后，它会出现一条消息'多部分标识符CTE2.STATUS可能不会受到限制所以我无法向前发展，因为我对于我能绑定的内容感到困惑。



DECLARE @ customerId INT，

@supplierId INT，

@subscriptionId INT



SET @customerId = 400 SET @supplierId = 1 SET @subscriptionId = 23471



; WITH CTE1 AS

（

SELECT derivedtbl2.fldSupplierId，derivedtbl2 .fldItemCode，derivedtbl2.fldIssueNumber，derivedtbl2.fldShipDate，derivedtbl2.fldStatus，derivedtbl2.fldIssueSequenceNumber，

tblSubscriptionSales_1.fldStatus AS子状态，tblSubscriptionSales_1.fldCustomerId

FROM

（SELECT TOP（100）PERCENT derivedtbl_1.fldSupplierId，derivedtbl_1.fldItemCode，tblSupplie rData_1.fldIssueNumber，tblSupplierData_1.fldShipDate，

tblSupplierData_1.fldStatus，tblSupplierData_1.fldIssueSequenceNumber

FROM

（SELECT fldSupplierId，fldItemCode

FROM tblSupplierData

WHERE（fldSupplierId = @supplierID）AND（fldSubscriptionId = @subscriptionId）

UNION ALL

SELECT fldSupplierId，fldItemCode <登记/>
从tblSubscriptionSales

，其中（fldSupplierId = @supplierID）AND（fldSubscriptionId = @subscriptionId）AND（fldCustomerId = @CustomerID））AS derivedtbl_1

INNER JOIN tblSupplierData AS tblSupplierData_1 ON derivedtbl_1.fldSupplierId = tblSupplierData_1.fldSupplierId

AND derivedtbl_1.fldItemCode = tblSupplierData_1.fldItemCode

ORDER BY tblSupplierData_1.fldShipDate DESC）AS derivedtbl2 INNER JOIN

tblSubscriptionSales AS tblSubscriptionSales_1 ON derivedtbl2.fldSupplierId = tblSubscriptionSales_1.fldSupplierId AND

derivedtbl2.fldItemCode = tblSubscriptionSales_1.fldItemCode

GROUP BY derivedtbl2.fldSupplierId，derivedtbl2.fldItemCode，derivedtbl2.fldIssueNumber，derivedtbl2.fldShipDate，derivedtbl2.fldStatus，derivedtbl2.fldIssueSequenceNumber，

tblSubscriptionSales_1 .fldStatus，tblSubscriptionSales_1.fldCustomerId

。具有（tblSubscriptionSales_1.fldCustomerId = @CustomerID）AND（tblSubscriptionSales_1.fldStatus = N'A'）OR

（tblSubscriptionSales_1.fldCustomerId = @CustomerID ）AND（tblSubscriptionSales_1.fldStatus = N'B'）或

（tblSubscriptionSales_1.fldCustomerId = @customerId）AND（tblSubscriptionSales_1.fldStatus = N'E'）或

（ tblSubscriptionSales_1.fldCustomerId = @CustomerID）AND（tblSubscriptionSales_1.fldStatus = N'F '）OR

（tblSubscriptionSales_1.fldCustomerId = @CustomerID）AND（tblSubscriptionSales_1.fldStatus = N'G'）OR

（tblSubscriptionSales_ 1.fldCustomerId = @customerId）AND（tblSubscriptionSales_1.fldStatus = N'P'）

），



CTE2 AS

（

SELECT fldIssueNumber，MAX（fldSupplierId）AS Expr1，MAX（fldItemCode）AS Expr2，fldStatus

FROM tblSupplierData

GROUP BY fldIssueNumber，fldSupplierId，fldSubscriptionId，fldStatus

。具有（fldSubscriptionId = @subscriptionId）AND（fldSupplierId = @supplierId）AND（fldStatus = N'Y'）

）中<无线电通信/>


UPDATE CTE2 SET CTE2.fldStatus ='N'来自CTE1 INNER JOIN CTE2 ON CTE2.Expr1 = CTE1.fldSupplierId和CTE2.Expr2 = CTE1.fldItemCode



SELECT tblSupplierData.fldSupplierId，tblSupplierData.fldItemCode

FROM tblSupplierData

WHERE（CTE2.fldStatus<> N'N'）

INNER JOIN CTE2 AS tblSupplierData.fldSupplierId = CTE2.fldSupplierId AND tblSupplierData.fldItemCode = CTE2.fldItemCode

At the end, it is coming up with a message 'MULTI-PART IDENTIFIER CTE2.STATUS COULD NOT BE BOUND" so I can't more forward as i'm confused as to what I can bind too.

DECLARE @customerId INT,
@supplierId INT,
@subscriptionId INT

SET @customerId = 400 SET @supplierId = 1 SET @subscriptionId = 23471

;WITH CTE1 AS
(
SELECT derivedtbl2.fldSupplierId, derivedtbl2.fldItemCode, derivedtbl2.fldIssueNumber, derivedtbl2.fldShipDate, derivedtbl2.fldStatus, derivedtbl2.fldIssueSequenceNumber,
tblSubscriptionSales_1.fldStatus AS SubStatus, tblSubscriptionSales_1.fldCustomerId
FROM
(SELECT TOP (100) PERCENT derivedtbl_1.fldSupplierId, derivedtbl_1.fldItemCode, tblSupplierData_1.fldIssueNumber, tblSupplierData_1.fldShipDate,
tblSupplierData_1.fldStatus, tblSupplierData_1.fldIssueSequenceNumber
FROM
(SELECT fldSupplierId, fldItemCode
FROM tblSupplierData
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId)
UNION ALL
SELECT fldSupplierId, fldItemCode
FROM tblSubscriptionSales
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId) AND (fldCustomerId = @customerID)) AS derivedtbl_1
INNER JOIN tblSupplierData AS tblSupplierData_1 ON derivedtbl_1.fldSupplierId = tblSupplierData_1.fldSupplierId
AND derivedtbl_1.fldItemCode = tblSupplierData_1.fldItemCode
ORDER BY tblSupplierData_1.fldShipDate DESC) AS derivedtbl2 INNER JOIN
tblSubscriptionSales AS tblSubscriptionSales_1 ON derivedtbl2.fldSupplierId = tblSubscriptionSales_1.fldSupplierId AND
derivedtbl2.fldItemCode = tblSubscriptionSales_1.fldItemCode
GROUP BY derivedtbl2.fldSupplierId, derivedtbl2.fldItemCode, derivedtbl2.fldIssueNumber, derivedtbl2.fldShipDate, derivedtbl2.fldStatus, derivedtbl2.fldIssueSequenceNumber,
tblSubscriptionSales_1.fldStatus, tblSubscriptionSales_1.fldCustomerId
HAVING (tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'A') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'B') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'E') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'F') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'G') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'P')
),

CTE2 AS
(
SELECT fldIssueNumber, MAX(fldSupplierId) AS Expr1, MAX(fldItemCode) AS Expr2, fldStatus
FROM tblSupplierData
GROUP BY fldIssueNumber, fldSupplierId, fldSubscriptionId, fldStatus
HAVING (fldSubscriptionId = @subscriptionId) AND (fldSupplierId = @supplierId) AND (fldStatus = N'Y')
)

UPDATE CTE2 SET CTE2.fldStatus = 'N' FROM CTE1 INNER JOIN CTE2 ON CTE2.Expr1 = CTE1.fldSupplierId AND CTE2.Expr2 = CTE1.fldItemCode

SELECT tblSupplierData.fldSupplierId, tblSupplierData.fldItemCode
FROM tblSupplierData
WHERE (CTE2.fldStatus <> N'N')
INNER JOIN CTE2 AS tblSupplierData.fldSupplierId = CTE2.fldSupplierId AND tblSupplierData.fldItemCode = CTE2.fldItemCode

这篇关于MSSQL - 特殊字符语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！