字段material_id,material_sid,qty,amt,customer_id,customer_group_nm,month,相同的material_id,material_sid,customer_id,customer_group_nm在不同的month中有不同的qty和amt,譬如:
material_sid customer_group_num month amt
11 a 2 1
11 a 7 2
11 a 3 3
22 b 1 1
22 b 4 2
只想得到月份最大的纪录,如material_id=11 的7月份的纪录和material_id=22的4月份的纪录,请教各位大侠SQL该怎么写
material_sid customer_group_num month amt
11 a 2 1
11 a 7 2
11 a 3 3
22 b 1 1
22 b 4 2
只想得到月份最大的纪录,如material_id=11 的7月份的纪录和material_id=22的4月份的纪录,请教各位大侠SQL该怎么写
(select material_sid,max(month) month from table group by material_sid) t where a.material_id=material_sid and a.month=t.month
select b.MATERIAL_SID,a.MATERIAL_ID,c.CUSTOMER_SID,d.INVOICED_DT,a.LFMON(月份字段),c.CUSTOMER_GROUP_NM,a.STANDARD_PRICE_AMT,d.BILLED_QTY
from base_valuation_ch a,ods_dim_material_1300 b,ods_dim_customer_1300 c,ods_sales_billing_dtl_1300 d
where a.MATERIAL_ID=b.MATERIAL_ID
and c.CUSTOMER_SID=d.BILL_TO_CUST_SID
and b.MATERIAL_SID=d.MATERIAL_SID
and a.LFGJA=2007
and c.CUSTOMER_GROUP_NM<>'-'
and d.INVOICED_DT>='20060801'
and d.INVOICED_DT<='20070731'
只想得到月份最大的纪录,不知道该如何实现