我们下面的例子以取 97号订单和133号订单里的数量最多的一行显示为例:
首先我们正常查询97号订单和133号订单如下图语句和截图所示:
select
ROW_NUMBER()over( order by DocEntry) as ID
, DocEntry as '订单号'
,LineNum+1 as 行号
,Quantity as 数量
from [POR1] where DocEntry in (97,133)
结果如下:
上面的结果ID是按DocEntry排序,现在我们增加partition进行按单号进行单号内ID标识,并且按照数量进行降序,这样得出结果ID为1的就是我们要的结果如下语句和截图
select
ROW_NUMBER()over(partition by DocEntry order by Quantity desc) as ID
,DocEntry as '订单号'
,LineNum+1 as 行号
,Quantity as 数量
from [POR1]
where DocEntry in (97,133)
结果如下
最终我们再筛选ID为1的行即可得出97和133号订单里数量最多的行
select 订单号,行号,数量
from (
select ROW_NUMBER()over(partition by DocEntry order by Quantity desc) as ID,DocEntry as '订单号',LineNum+1 as 行号,Quantity as 数量 from [POR1]
where DocEntry in (97,133)
)AA where ID=1
结果如下: