本文介绍了sql group by和max以及其他值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张包含以下内容的表:
i have a table that contains:
itemid inventdimid datephysical transrefid 10001 123 2015-01-02 300002 10002 123 2015-01-03 3566 10001 123 2015-02-05 55555 10002 124 2015-02-01 4545我想要的结果
itemid inventdimid datephysical transrefid 10001 123 2015-02-05 555 10002 123 2015-01-03 3566 10002 124 2015-02-01 4545MY查询:
MY query:
SELECT a.itemid,a.inventdimid,max(a.datephysical),a.transrefid FROM a where dataareaid = 'ermi' group by a.itemid,a.inventdimid它在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。
it is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
推荐答案使用ANSI标准 row_number() 功能:
Use the ANSI standard row_number() function:
select t.* from (select t.*, row_number() over (partition by itemid, inventdimid order by datephysical desc) as seqnum from table t ) t where seqnum = 1;更多推荐
sql group by和max以及其他值
发布评论