SQL Server 2008 Msg 8120 错误的解决

这几天写存储过程的时候碰到一个8120错误

 

 

数据库中两张表，一张Journeys表，一张journeyLegs表

我需要Journeys表中全部列的信息，同时需要某个journey的leg数目，我使用如下语句，

 

select Journeys.* , MAX(JourneyLegs.JourneyLegNo)
from Journeys
inner join JourneyLegs
on Journeys.JourneyId = JourneyLegs.JourneyId
Group by Journeys.JourneyId

 

返回如下错误，

Msg 8120, Level 16, State 1, Line 1
Column 'Journeys.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

由于JourneyId是主键， Group by Journeys.JourneyId, Journeys.Name实际并不会对结果产生影响，为什么数据库不能自动识别呢？关键在于join的处理方式，

在处理inner join语句时候，数据库

. the required tables are joined
. the composite dataset is filtered through the WHERE clause
. the remaining rows are chopped into groups by the GROUP BY clause, and aggregated
. they are then filtered again, through the HAVING clause
. finally operated on, by SELECT / ORDER BY, UPDATE or DELETE.

 

在表被连接时，可以假设一个新的临时表被创建，这个新表的列就是被GROUP BY 子句中的列以及被AGGREGATION的列，

查看一下连接http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

 

一个结局办法如下，并且性能也会提高：

select j.* , legno
from Journeys as j
inner join (select JourneyId, MAX(JourneyLegNo) as legno from JourneyLegs group by JourneyId) as jl
on j.JourneyId = jl.JourneyId