请帮我改正 [英] Please help me to correct this

问题描述

除非聚合位于HAVING子句或选择列表中包含的子查询中，并且聚合的列是外部引用，否则聚合不能出现在ON子句中.

查询是这个

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

The query is this

DECLARE @ischecked BIT =1

SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid AND MAX(ticketdetail.createddate)=1	LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketdetail.createddate

select * from ticketmaster

推荐答案

错误应该是由于AND MAX(ticketdetail.createddate)=1
试试:

Error should be because of AND MAX(ticketdetail.createddate)=1
Try:

DECLARE @ischecked BIT =1
 
SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketdetail.createddate
HAVING MAX(ticketdetail.createddate)=1 

select * from ticketmaster

详细信息在这里:
http://www.sql-server-performance.com/2007 /aggregate-cannot-appear-in-on-clause/ [ http://exacthelp.blogspot.in/2012/04/aggregate-无法出现在clause.html中 [ ^ ]

Details here:
http://www.sql-server-performance.com/2007/aggregate-cannot-appear-in-on-clause/[^]
http://exacthelp.blogspot.in/2012/04/aggregate-cannot-appear-in-on-clause.html[^]

此处已更新查询:

Here is updated Query :

DECLARE @ischecked BIT =1
 
SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid 
LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
HAVING MAX(ticketdetail.createddate)=1

 
select * from ticketmaster

希望这会有所帮助，如果是，那么请接受并投票答复.任何对此的任何疑问/问题都将受到欢迎.

谢谢&问候
RDBurmon.Sr.软件工程师

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

这篇关于请帮我改正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！