我需要计算每个月中向某些客户开具发票的日期数
考虑名为Table1的表
Branch Month Date Amount
----------------------------------------
B1 April 01/04/20 10000
B1 April 14/04/20 13000
B1 May 01/05/20 25000
B1 May 14/05/20 23000
我尝试了以下代码
Select
Branch, Month, Date, Amount,
Row_Number() over (partition by Branch order by Month) as rowcount
from table1
结果是
Branch Month Date Amount rowcount
----------------------------------------
B1 April 01/04/20 10000 1
B1 April 14/04/20 13000 2
B1 May 01/05/20 25000 3
B1 May 14/05/20 23000 4
我需要的结果应该基于列月份对名为branch的列进行计数,所需结果是
Branch Month Date Amount rowcount
---------------------------------------------
B1 April 01/04/20 10000 1
B1 April 14/04/20 13000 2
B1 May 01/05/20 25000 1
B1 May 14/05/20 23000 2
这里的行数基于列分支和月份,我如何获得此结果?
您需要按进行分区month
。像这样:
row_number() over (partition by Branch, month order by Month)
请注意month
重复。一个order by
需要在SQL Server中,所以你需要的东西。其他选项是:
row_number() over (partition by Branch, month order by (select null))
row_number() over (partition by Branch, month order by date)
我怀疑最后是您真正想要的。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句