呼叫MS ACCESS高手，group by 为啥不能用alias name

jl162401

SELECT
a.material_code
,year(a.Consumption_Date)  
,month(a.Consumption_Date)  
,sum(a.consumption_qty) as sum0
,sum(nz(b.quantity,0)) as sum1
,(sum0 - sum1)
,(IIF(IsNULL(c.material),' ','Scrapped')) as flg
from ( SAP_Consumption a
left join
SMOG_Special_Sales b
on a.material_code  = cstr(b.code)
and month(a.Consumption_Date) = month(b.Delivery_Date)
and year(a.Consumption_Date) = year(b.Delivery_Date) )
left join (select distinct material from Scrapped_Blocked_list
where code_blocked_C1 = 'Y') c
on a.material_code = c.material
GROUP BY material_code
,year(a.Consumption_Date)
,month(a.Consumption_Date)
,flg

这样运行会报错
"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"

但是如果我把(IIF(IsNULL(c.material),' ','Scrapped')) 直接放在Group by里面，是可以得到正确结果

SELECT
a.material_code
,year(a.Consumption_Date)  
,month(a.Consumption_Date)  
,sum(a.consumption_qty) as sum0
,sum(nz(b.quantity,0)) as sum1
,(sum0 - sum1)
,(IIF(IsNULL(c.material),' ','Scrapped')) as flg
from ( SAP_Consumption a
left join
SMOG_Special_Sales b
on a.material_code  = cstr(b.code)
and month(a.Consumption_Date) = month(b.Delivery_Date)
and year(a.Consumption_Date) = year(b.Delivery_Date) )
left join (select distinct material from Scrapped_Blocked_list
where code_blocked_C1 = 'Y') c
on a.material_code = c.material
GROUP BY material_code
,year(a.Consumption_Date)
,month(a.Consumption_Date)
,(IIF(IsNULL(c.material),' ','Scrapped'))

为何不能用alias name在group by 里面 ？

谁有MS ACCESS的SQL语法大全啊，能否share一下，多谢多谢

jl162401

没人知道啊

jl162401

我把问题简化一下：

select material_code as mc1,sum(consumption_qty) as sum1
from SAP_Consumption a
group by mc1

为啥我不能用alias name mc1在这个简单查询里面？

jl162401

看来就是不能用

If you want to avoid the mess of the case statement being in your query twice, you may want to place it in a User-Defined-Function.

Sorry, but SQL Server would not render the dataset before the Group By clause so the column alias is not available

wTam

Try this.  This works in Oracle

select material_code as mc1,sum(consumption_qty) as sum1
from SAP_Consumption a
group by material_code

rumcoke

lz的例子 就说明了直接用不行 可以尝试加一层子查询,
select a.* from
( #your actual query eg.
   select xxx as name from table ) a
group by a.name
如果你的目的是group by语句简洁 这样就达到目的了

[ 本帖最后由 rumcoke 于 2010-8-6 15:13 编辑 ]