时间戳

首页 > 解决方案 > CTE 语句中不存在列名

sql-server - CTE 语句中不存在列名

问题描述

我正在尝试创建一个 CTE 语句：

WITH StartCash (StartCash) AS
(
    SELECT StartCash 
    FROM CashierInfo 
    WHERE CashierID = (SELECT MAX( CashierID) 
                       FROM CashierInfo  
                       WHERE UserID = 1 AND EndDate IS NULL)
)
SELECT
    StartCash, a.username AS Username, b.Adress AS Adress,
    (SUM(c.quantity * c.discountprice)) AS SumPrice,
    c.Printed AS Printed, c.CashierUserID AS CashierUserID,
    c.RetailDelivery AS RetailDelivery, c.TrnDocumentID 
FROM
    Users a 
JOIN
    InventoryTransTemp c ON c.CashierUserID = a.UserID 
JOIN
    DeliveryAdress b ON b.DeliveryAdressID = c.DeliveryAdressID 
WHERE
    c.cashieruserid =  1 
GROUP BY
    a.Username, b.Adress, c.Printed, 
    c.CashierUserID, c.RetailDelivery, c.TrnDocumentID

但我收到一个错误

列名“StartCash”无效

标签： sql-serversql-server-2008

解决方案

StartCash不存在，因为您的 CTE 未包含在 FROM 子句中。

仅根据我在您的问题中看到的内容，我认为CashierInfo.UserID加入Users.UserID. 所以尝试这样的事情：

WITH StartCash
AS (
    SELECT StartCash
        ,UserID
    FROM CashierInfo
    WHERE CashierID = (
            SELECT MAX(CashierID)
            FROM CashierInfo
            WHERE UserID = 1
                AND EndDate IS NULL
            )
    )
SELECT sc.StartCash
    ,a.username AS Username
    ,b.Adress AS Adress
    ,(SUM(c.quantity * c.discountprice)) AS SumPrice
    ,c.Printed AS Printed
    ,c.CashierUserID AS CashierUserID
    ,c.RetailDelivery AS RetailDelivery
    ,c.TrnDocumentID
FROM StartCash sc
INNER JOIN Users a ON sc.UserID = a.UserID
INNER JOIN InventoryTransTemp c ON c.CashierUserID = a.UserID
INNER JOIN DeliveryAdress b ON b.DeliveryAdressID = c.DeliveryAdressID
WHERE c.cashieruserid = 1
GROUP BY sc.StartCash
    ,a.Username
    ,b.Adress
    ,c.Printed
    ,c.CashierUserID
    ,c.RetailDelivery
    ,c.TrnDocumentID

推荐阅读