表A
CNID QTY ITEMID TDID
A001 200 A W
A004 200 A S
A008 150 A T
A002 120 B C
A002 150 B N
A003 230 C B
... ... ... ...
按ITEMID 的數量從大到小排序,按ITEMID分組分別取出一條記錄,
如果數量相等,ITEMID又相同的,就任取其中一條
效果如下:
CNID QTY ITEMID TDID
A001 200 A W
A002 150 B N
A003 230 C B
... ... ... ...
CNID QTY ITEMID TDID
A001 200 A W
A004 200 A S
A008 150 A T
A002 120 B C
A002 150 B N
A003 230 C B
... ... ... ...
按ITEMID 的數量從大到小排序,按ITEMID分組分別取出一條記錄,
如果數量相等,ITEMID又相同的,就任取其中一條
效果如下:
CNID QTY ITEMID TDID
A001 200 A W
A002 150 B N
A003 230 C B
... ... ... ...
select max(CNID) as CNID , QTY ,ITEMID ,max(TDID ) as TDID
from table group by ITEMID ,QTY
order by QTY desc
from (select row_number() over(partition by ITEMID order by QTY desc) no,* from tb)a
where no=1