聚合不应出现在 WHERE 子句中

[tongchou]

CREATE procedure T_list

--资讯新闻百万级分页
(
@StrWhere varchar(100),
@PageSize int,
@PageIndex int
)

AS

declare @strSQL varchar(2000) -- 主语句
declare @strCountSQL varchar(2000) -- 总记录主语句
declare @strTmp varchar(1000) -- 临时变量

Set @strTmp =" Select top " + str(@PageSize) + " yixiang_productshow.id,yixiang_productshow.cpmc,yixiang_productshow.cpcd,yixiang_productshow.cpjg,yixiang_productshow.picture,gsid,yixiang_productshow.cpxh,yixiang_corporation.qymc,yixiang_corporation.name,yixiang_corporation.phone FROM yixiang_productshow INNER JOIN yixiang_corporation ON yixiang_productshow.gsid=yixiang_corporation.id "

if @StrWhere<>''
Begin

Set @strSQL=@strTmp + " where ID < (select min(ID) from (select top " + str((@PageIndex-1)*@PageSize)+" ID from yixiang_productshow Where "+@StrWhere+" order by ID desc) as tblTmp ) and "+@StrWhere+" order by ID desc"




聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

print @strSQL

set @strCountSQL="select count(yixiang_productshow.ID) as countx from yixiang_productshow INNER JOIN yixiang_corporation ON yixiang_productshow.gsid=yixiang_corporation.id Where "+@StrWhere+" "


End

else
Begin
Set @strSQL=@strTmp + " where yixiang_productshow.ID < (select min(yixiang_productshow.ID) from (select top " + str((@PageIndex-1)*@PageSize)+" yixiang_productshow.ID from yixiang_productshow INNER JOIN yixiang_corporation ON yixiang_productshow.gsid=yixiang_corporation.id order by yixiang_productshow.ID desc) as tblTmp ) order by yixiang_productshow.ID desc"
set @strCountSQL="select count(yixiang_productshow.ID) as countx from yixiang_productshow INNER JOIN yixiang_corporation ON yixiang_productshow.gsid=yixiang_corporation.id "

End






if @PageIndex = 1
if @StrWhere<>''
Begin

Set @strSQL=@strTmp +" Where "+@StrWhere+" order by yixiang_productshow .ID desc"

End
else
Begin

Set @strSQL=@strTmp +" order by yixiang_productshow .ID desc"

End

exec (@strSQL)




exec (@strCountSQL)
GO

[haitao]

贴出 print @sql 的结果 一般是在分组后再对子查询进行where过滤

[tongchou]

Select top 200 yixiang_productshow.id,yixiang_productshow.cpmc,yixiang_productshow.cpcd,yixiang_productshow.cpjg,yixiang_productshow.picture,gsid,yixiang_productshow.cpxh,yixiang_corporation.qymc,yixiang_corporation.name,yixiang_corporation.phone FROM yixiang_productshow INNER JOIN yixiang_corporation ON yixiang_productshow.gsid=yixiang_corporation.id where yixiang_productshow.ID < (select min(yixiang_productshow.ID) from (select top 200yixiang_productshow. ID from yixiang_productshow Where yixiang_productshow.sortid=28 order by yixiang_productshow.ID desc) as tblTmp ) and yixiang_productshow.sortid=28 order by yixiang_productshow.ID desc 服务器: 消息 147，级别 16，状态 2，行 1 聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。 （所影响的行数为 1 行）

[tongchou]

能否帮我修改下，实在不知道怎么修改了，搞了一上午了都不行

[KevinLiu]

聚合如果过滤的话要用HAVING语法，不是WHERE语法

[發糞塗牆]

要么用having，要么在where中用子查询。

[tongchou]

那位大神帮我修改下嘛 ＼