请问Access中5层left outer join的一个古怪问题

ilovefyl 2006-04-27 06:14:05

我的查询是
SELECT
t_act_subcategory.subcategory_name,
t_subactivity.subactivity_id as SubID,
t_vendorlist.vendor_description as Vendor,
t_act_subcategory.subcategory_id as CatID,
t_act_subcategory.subcategory_name as CatName,
t_subactivity.title as Title,
t_subactivity.internalcategory_id as InternalCategory,
t_subactivity.option_id,
SUM(t_subactivity_phase_info.budget) AS Totalbudget,
t_subactivity_estimate_info.estimate_date,
t_subactivity_estimate_info.estimate_number,
t_subactivity_estimate_info.estimate_value,
t_subactivity_estimate_info.accessment_value
FROM
(
(
(
(t_act_subcategory left outer join t_subactivity on
(t_subactivity.subcategory_id = t_act_subcategory.subcategory_id
and t_subactivity.activity_id = '05-A008'
and t_subactivity.status_id <> 'ST007'
and t_subactivity.option_id ='OPT002')
) left outer join t_vendorlist on
(t_subactivity.vendor_id = t_vendorlist.vendor_id)
) left outer join t_subactivity_phase_info on
(t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)
) left outer join t_subactivity_estimate_info on
(t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)
)
GROUP BY
t_act_subcategory.subcategory_name,
t_subactivity.subactivity_id ,
t_vendorlist.vendor_description ,
t_act_subcategory.subcategory_id ,
t_act_subcategory.subcategory_name ,
t_subactivity.title ,
t_subactivity.internalcategory_id ,
t_subactivity.option_id,
t_subactivity_estimate_info.estimate_date,
t_subactivity_estimate_info.estimate_number,
t_subactivity_estimate_info.estimate_value,
t_subactivity_estimate_info.accessment_value
ORDER BY
1,
4,
2,
10

这个查询的结果是t_subactivity.activity_id 不是'05-A008'也出来了，出来了很多无关的数据
假设只留from条件执行：
SELECT
*
FROM
(
(
(
(t_act_subcategory left outer join t_subactivity on
(t_subactivity.subcategory_id = t_act_subcategory.subcategory_id
and t_subactivity.activity_id = '05-A008'
and t_subactivity.status_id <> 'ST007'
and t_subactivity.option_id ='OPT002')
) left outer join t_vendorlist on
(t_subactivity.vendor_id = t_vendorlist.vendor_id)
) left outer join t_subactivity_phase_info on
(t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)
) left outer join t_subactivity_estimate_info on
(t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)
)
这样的结果是对的，很奇怪
假设在第一个sql中将[and t_subactivity.status_id <> 'ST007' ]删除进行查询
那么数据的个数又恢复正常。

将sql变化一下，group by 去掉
SELECT
t_act_subcategory.subcategory_name,
t_subactivity.subactivity_id as SubID,
t_vendorlist.vendor_description as Vendor,
t_act_subcategory.subcategory_id as CatID,
t_act_subcategory.subcategory_name as CatName,
t_subactivity.title as Title,
t_subactivity.internalcategory_id as InternalCategory,
t_subactivity.option_id,
t_subactivity_estimate_info.estimate_date,
t_subactivity_estimate_info.estimate_number,
t_subactivity_estimate_info.estimate_value,
t_subactivity_estimate_info.accessment_value
FROM
(
(
(
(t_act_subcategory left outer join t_subactivity on
(t_subactivity.subcategory_id = t_act_subcategory.subcategory_id
and t_subactivity.activity_id = '05-A008'
and t_subactivity.status_id <> 'ST007'
and t_subactivity.option_id ='OPT002')
) left outer join t_vendorlist on
(t_subactivity.vendor_id = t_vendorlist.vendor_id)
) left outer join t_subactivity_phase_info on
(t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)
) left outer join t_subactivity_estimate_info on
(t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)
)
这样的结果还是错的，但是将select中的<t_subactivity.internalcategory_id as InternalCategory,>
去掉，执行的结果又是对的
完全不能解释了
按道理来说，最终结果的件数只应该和join条件和group by 有关，怎么会和select 中的东西有关呢
那么最后只留一个select项目，也就是刚才去除后数据正确的项目
SELECT
t_subactivity.internalcategory_id as InternalCategory
FROM
(
(
(
(t_act_subcategory left outer join t_subactivity on
(t_subactivity.subcategory_id = t_act_subcategory.subcategory_id
and t_subactivity.activity_id = '05-A008'
and t_subactivity.status_id <> 'ST007'
and t_subactivity.option_id ='OPT002')
) left outer join t_vendorlist on
(t_subactivity.vendor_id = t_vendorlist.vendor_id)
) left outer join t_subactivity_phase_info on
(t_subactivity.subactivity_id=t_subactivity_phase_info.subactivity_id)
) left outer join t_subactivity_estimate_info on
(t_subactivity.subactivity_id=t_subactivity_estimate_info.subactivity_id)
)
结果的件数是正确的。。晕哦
请大家帮忙看看。。。多谢~~！

...全文