oracle中分组求和后查询其他列应用场景

        最近有个需求，对仓库，库区，货位，检验编码相同的数据进行分组然后统计每组的实物库存，但是查询列还有其它列，group by是单分组函数，使用group by就不能查询分组以外的字段，在网上查阅了很多文章发现都没有详解，然后自己总结了这篇文章，希望对此刻正在找解决办法的你有所帮助。

	SELECT
		k.ID,
		k.MOMWAREHOUSEID,
		k.MOMRESERVOIRAREAID,
		k.MOMGOODSALLOCATIONID,
		k.MOMMATERIALID,
		k.MOMINSPECTIONCODE,
		c.MOMWAREHOUSENAME,
		q.MOMRESERVOIRAREANAME,
		h.MOMGOODSALLOCATION,
		w.MOMMATERIALCODE,
		w.MOMMATERIALNAME,
		w.MOMMARK,
		w.MOMSPECIFICATIONS,
		k.MOMOVERDUEIDENTIFICATION,
		to_char(k.MOMPERIODVALIDITY,'YYYY-MM-DD HH24:MI:SS') MOMPERIODVALIDITY,
		sum(nvl( k.MOMPHYSICALLNVENTORY, 0 )) over ( PARTITION BY k.MOMWAREHOUSEID, k.MOMRESERVOIRAREAID, k.MOMGOODSALLOCATIONID,
	k.MOMMATERIALID,k.MOMINSPECTIONCODE	) MOMPHYSICALLNVENTORY,
		sum(nvl( k.MOMBAKINGCOUNT, 0 )) over ( PARTITION BY k.MOMWAREHOUSEID, k.MOMRESERVOIRAREAID, k.MOMGOODSALLOCATIONID, k.MOMMATERIALID,k.MOMINSPECTIONCODE ) MOMBAKINGCOUNT,
		row_number () over ( PARTITION BY k.MOMWAREHOUSEID, k.MOMRESERVOIRAREAID, k.MOMGOODSALLOCATIONID, k.MOMMATERIALID,
	k.MOMINSPECTIONCODE ORDER BY k.MOMBAKINGCOUNT ) AS rn 
	FROM
		DGMOMPTDGMOMGLLJJCKGLKCMX k
		LEFT JOIN DGMOMPTDGMOMGLLJJCKGLCKXX c ON k.MOMWAREHOUSEID = c.ID
		LEFT JOIN DGMOMPTDGMOMGLLJJCKGLKQXX q ON k.MOMRESERVOIRAREAID = q.ID
		LEFT JOIN DGMOMPTDGMOMGLLJJCKGLHWXX h ON k.MOMGOODSALLOCATIONID = h.ID
		LEFT JOIN DGMOMPTDGMOMGLLJJCKGLJCSJWH w ON k.MOMMATERIALID = w.ID
		WHERE k.MOMDATASTATE = 0
		AND c.MOMDATASTATE = 0
		AND q.MOMDATASTATE = 0
		AND h.MOMDATASTATE = 0
		AND w.MOMDATASTATE = 0
		AND k.MOMPHYSICALLNVENTORY > 0 
		AND k.MOMPERIODVALIDITY < SYSDATE

以上代码执行的结果：

最终取行号为1的就实现了分组求和

总结：

over（）搭配row_number () 时需要使用ORDER BY，这样会记录每组数据的行号

over（）搭配聚合函数时不需要使用ORDER BY，如果使用ORDER BY 那么聚合函数里面的值会依次计算，可根据实际需求自行修改