这几天写存储过程的时候碰到一个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