Sql Server CTE“无法绑定多部分标识符”。 [英] Sql Server CTE "multi- part identifier could not be bound."

问题描述

我对同一个cte查询的最后一个问题回答得如此之快，我想我会在您的sql专家面前跳出下一个问题。如果我可以回顾我的基本逻辑，然后显示我的代码和语法错误，则将不胜感激。

My last question on this same cte query was answered so quickly, I thought I'd bounce this next one off you sql gurus. If I could go over my basic logic, then show my code and syntax error, any help would be greatly appreciated..

我为股票交易系统提供了三个表格：符号表：顾名思义，它是股票代码的列表，每日定价/交易量表：再次如前所述，每个记录都有日期字段和符号字段以及定价信息，最后还有交易日期表：查询中所有交易日期的参考。

I have three tables for a stock trading system: a symbols table: as the name suggests it's a list of ticker symbols, a daily pricing/ volume table: again, as- described, and each record has a date field and symbol field as well as the pricing info, and lastly a trading dates table: the reference for all trading dates in our query.

我想返回一个包含两个字段的记录集：符号和日期。该对代表在价格量表中没有该交易品种相应价格/交易量数据的所有交易日期和交易品种。有道理？在查询中，我收到错误消息：无法绑定多部分标识符 Symb.Symbol。这是我的cte查询：

I'd like to return a recordset with two fields: a symbol and a date. The pair represents all trading dates and symbols that don't have corresponding pricing/ vol data for that symbol in the pricing volume table. Make sense? On my query, I'm getting the error message: "The multi-part identifier "Symb.Symbol" could not be bound." Here's my cte query:

WITH Symb AS
(
     SELECT Symbol
     FROM tblSymbolsMain
),

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol
     WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),

WideDateRange AS
(
     SELECT TradingDate
     FROM tblTradingDays
     WHERE (TradingDate >= dbo.NextAvailableDataDownloadDateTime()) AND (TradingDate <= dbo.LatestAvailableDataDownloadDateTime())
),

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded)
)

SELECT Symb.Symbol, DatesNeeded.TradingDate
FROM Symb CROSS JOIN DatesNeeded

推荐答案

此：

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol
     WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),

需要的是：

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol inner join Symb on
         tblDailyPricingAndVol.Symbol = Symb.Symbol
),

但是您的查询仍然无法正常工作，由于以下原因：

But your query still won't work, since this:

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded)
)

需要这样：

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded d where d.Date = wdr.TradingDate)
)

但是实际上，您可以在没有CTE的情况下执行此操作，例如：

But really, you can do this without CTEs, like this:

select
    sm.Symbol,
    tb.TradingDate
from
    tblSymbolsMain sm
    cross join tblTradingDays tb
    left join tblDailyPricingAndVol dp on
        sm.Symbol = dp.Symbol 
        and tb.TradingDate = dp.Date
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime()
        and dbo.NextAvailableDataDownloadDatetime()
    and dp.Date is null

此查询从 tblSymbolsMain 中获取所有符号，并从 tblTradingDays 。然后对 tblDailyPricingAndVol 进行左连接，并过滤出找到匹配项的任何行。

This query grabs all the symbols from tblSymbolsMain and all the dates between your last and next available dates from tblTradingDays. Then it does a left join on tblDailyPricingAndVol and filters out any row that found a match.

您也可以使用不存在代替左联接想想也更清楚一些：

You could also use not exists in lieu of a left join, which I think is a bit clearer, too:

select
    sm.Symbol,
    tb.TradingDate
from
    tblSymbolsMain sm
    cross join tblTradingDays tb
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime() 
        and dbo.NextAvailableDataDownloadDatetime()
    and not exists (
        select
            1
        from
            tblDailyPricingAndVol dp
        where
            dp.Symbol = sm.Symbol
            and dp.Date = tb.TradingDate
    )

这篇关于Sql Server CTE“无法绑定多部分标识符”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！