首页 文章

Sql Server cte错误“子查询返回了多个值 . ”

提问于
浏览 次
1

这个问题是对我在其他近期帖子中提到的我的第一个cte进行编码的同样努力的延续 . 简而言之，我正在为下面的查询解决几个编译器错误 . 我现在收到错误“Subquery返回了多个值 . 当子查询跟随=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做 . ”但根据我目前的能力，我在下面提出的内容对我来说似乎是“合法的”......任何帮助都会很棒 . BTW我得到了这个错误，或者当我尝试运行这个时Visual Studio 2010关闭了......

WITH Symb AS
(
     SELECT Symbol
     FROM tblSymbolsMain
),

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol inner join Symb on
         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 d where d.Date = wdr.TradingDate)
)

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

我的职能，如要求：

ALTER FUNCTION dbo.LatestAvailableDataDownloadDateTime()
    RETURNS date
BEGIN
    RETURN (SELECT DATEADD(hour, 18, MAX(TradingDate)) AS LatestTradingDateAvailForDL 
    FROM tblTradingDays
    GROUP BY TradingDate
    HAVING (DATEADD(hour, 18, MAX(TradingDate)) < GETDATE()))
END

ALTER FUNCTION dbo.NextAvailableDataDownloadDateTime()
    RETURNS date
BEGIN
    RETURN (SELECT DATEADD(hour, 18, MIN(TradingDate)) AS TrDate
    FROM tblTradingDays
    HAVING (DATEADD(hour, 18, MIN(TradingDate)) > dbo.LatestDataDownloadDate()))
END
sql sql-server common-table-expression

2 回答

  • -2

    您的函数返回多个值 . 试试这个：

    ALTER FUNCTION dbo.LatestAvailableDataDownloadDateTime()
    RETURNS date
BEGIN
    RETURN (SELECT DATEADD(hour, 18, MAX(TradingDate)) AS LatestTradingDateAvailForDL 
    FROM tblTradingDays
    WHERE (DATEADD(hour, 18, TradingDate) < GETDATE()))
END

    和：

    ALTER FUNCTION dbo.NextAvailableDataDownloadDateTime()
    RETURNS date
BEGIN
    RETURN (SELECT DATEADD(hour, 18, MIN(TradingDate)) AS TrDate
    FROM tblTradingDays
    WHERE (DATEADD(hour, 18, TradingDate) > dbo.LatestDataDownloadDate()))
END
    回复于
  • 2

    您的subselect有一个select *，它返回所有列 . subselects需要处理一列和一行，即：
    从carype = 'Honda'的汽车中选择cartype

    祝你好运，发言

    回复于

相关问题