group by 为啥不能用alias name

liujchen

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 里面 ？

liujchen

有人知道吗，多谢多谢

liujchen

我把问题简化一下：
select material_code as mc1,sum(consumption_qty) as sum1
from SAP_Consumption a
group by mc1

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

liujchen

看来就是不能用

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.

renqiang851224

因为group by 的操作对象是原表

capricorn

5楼回答是正解！我刚刚学了sql，心得是group by是对from的表进行分组，而select后的字段名是新表的字段名，原表中不存在。