|
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一下，多谢多谢 |
