标识符不能绑定在多个公用表表达式中 [英] Identifier could not be bound in multiple common table expressions

问题描述

我正在使用使用SQL和Excel进行数据分析"一书中的SQLBook数据库来显示州的平均发货天数，这些州的平均发货天数高于平均发货天数.我正在使用2种常见的表格表达式:

I'm using the SQLBook database from the Data Analysis Using SQL and Excel book to display the average days to ship for states that have higher than average overall shipping days. I'm using 2 common table expressions:

WITH orderDetails (days, state) 
AS(
    SELECT DATEDIFF(day, o.OrderDate, ol.ShipDate), o.State 
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)
,
/* This finds the overall average shipping days */
AvgShipping (avgShip)
AS(
    SELECT AVG(DATEDIFF(day, o.OrderDate, ol.ShipDate))
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)

SELECT
    state,
    AVG(days) AS "Average days to ship"
FROM orderDetails
GROUP BY state
HAVING AVG(days) > AvgShipping.avgShip
ORDER BY state

问题是我不断收到多部分标识符" AvgShipping.avgShip"，无法绑定"错误.请让我知道此查询的问题所在.

The problem is I keep getting a "The multi-part identifier "AvgShipping.avgShip" could not be bound" error. Please let me know where the problem with this query is.

谢谢！

推荐答案

您不在任何地方选择AvgShpping.试试

You're not selecting from AvgShpping anywhere. Try

WITH orderDetails (days, state) 
AS(
    SELECT DATEDIFF(day, o.OrderDate, ol.ShipDate), o.State 
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)
,
/* This finds the overall average shipping days */
AvgShipping (avgShip)
AS(
    SELECT AVG(DATEDIFF(day, o.OrderDate, ol.ShipDate))
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)

SELECT
    state,
    AVG(days) AS "Average days to ship"
FROM orderDetails
GROUP BY state
HAVING AVG(days) > (select avgShip from AvgShipping) 
ORDER BY state

这篇关于标识符不能绑定在多个公用表表达式中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！