如何改进关键字'select'附近的'语法不正确'。

this query give me 'Incorrect syntax near the keyword 'select'.' look please below bold character area.

此查询为我提供了'关键字'select'附近的语法错误。请看下面粗体字区域。

declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int
select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar'
set @sum = select Sum(t.[VISITINGCOUNT]) from
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t    

select 100*(t.[VISITINGCOUNT]/@sum),t.[DATE] from        
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t

5 个解决方案

#1

I don't believe you can have SET @var = SELECT ...

我不相信你可以有SET @var = SELECT ...

Try the following instead:

请尝试以下方法:

SELECT @sum = Sum(t.[VISITINGCOUNT]) from ...

I would also like to say that seeing as all you are doing is SUM-ing a single column in your sub-query there is no point in doing the group by, or returning the second column.

我还想说,看到你正在做的就是在你的子查询中对单个列进行求和,没有必要再进行分组,或者返回第二列。

You are basically counting the number of records in various groups, then adding all the groups together. Far quicker just to count the total number of pages in total if that is all you need.

您基本上计算各组中的记录数,然后将所有组添加到一起。如果您需要的话,只需计算总页数就可以更快。

The following would be much simpler:

以下内容会简单得多:

SELECT @sum = COUNT(page) FROM scr_StatisticaLog                
   WHERE Date BETWEEN @date1 AND  @date2  
   AND (Page=@page OR  @page='Tüm Sayfalar') AND ProcessType='PageView'

#2

Try changing

set @sum = select Sum(t.[VISITINGCOUNT]) from

select @sum = Sum(t.[VISITINGCOUNT]) from

Haven't tested it works though, just that it parses correctly without errors.

没有测试它的工作原理,只是它正确解析没有错误。

#3

declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int
select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar'

--Changed here
select @sum = Sum(t.[VISITINGCOUNT]) from
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t    




select 100*(t.[VISITINGCOUNT]/@sum),t.[DATE] from        
(        
select count(page) as [VISITINGCOUNT],                 
     cast(DATENAME ( year ,DATE)+'-'+DATENAME (month ,DATE)+
     '-'+DATENAME (day ,DATE) as smalldatetime)  as [DATE]               
    from scr_StatisticaLog                
    where Date between @date1 and  @date2  
          and (Page=@page or  @page='Tüm Sayfalar')  and ProcessType='PageView'           
GROUP BY 
cast(DATENAME ( year ,DATE)+
'-'+DATENAME (month ,DATE)+
'-'+DATENAME (day ,DATE) as smalldatetime)) as t

#4

If you insist on sticking with SET, then wrap the entire subquery in parentheses ()

如果你坚持使用SET,那么将整个子查询包装在括号中()

set @sum = ( select Sum(t.[VISITINGCOUNT]) from ....... )

设置@sum =(从.......中选择Sum(t。[VISITINGCOUNT])

otherwise change the variable assignment to a 'SELECT @var =' as suggested by other answers

否则将变量赋值更改为“SELECT @var =”,如其他答案所示

#5

If you are executing this from SSMS, you should try enabling the "SQLCMD Mode" from the menu before executing the script.

如果从SSMS执行此操作,则应在执行脚本之前尝试从菜单中启用“SQLCMD模式”。

#1