Calculating a “Row X of Y”

显示 “Row X of Y,” ，X是当前行，Y是总行数， 
那就是 ROW_NUMBER(ORDER BY stor_id) of Count(*) OVER()
此处还是以样例数据库 pub 为例说明

 

select stor_id, ord_date, qty,
           stor_id_RowID = ROW_NUMBER() OVER(PARTITION BY stor_id order by stor_id,ord_date),
           --stor_id_RowID = ROW_NUMBER() OVER(PARTITION BY stor_id order by stor_id),
           --stor_id_RowQty = COUNT(*) OVER(ORDER BY stor_id),  --此处不是想要的结果，这里想要只显示stor_id的总数，但是又不想用分组，可是不分组这个数又怎么来呢
           stor_id_RowQty = COUNT(*) OVER(ORDER BY stor_id),    --想得到分组后的总行数(但是不要用分组，形式上没分组，实际是分组了的结果，似乎可行的啊，用蒙了...)
           RowID = ROW_NUMBER() OVER(ORDER BY stor_id,ord_date),  --X
           RowQty  = COUNT(*) OVER()                              --Y
from sales
order by stor_id,ord_date;

运行结果：