sql-server - 如何根据sql中的另一列获取一列中递增的值

Question

我有下表记录。对于每个 TranNo，我都必须在预期结果列中加一。可能吗？

如何在sqlserver中得到这个结果？

我无法获得适当的结果

 <Expected Result>
1
1
2
3
4
5
6
7
8

我尝试过:

SELECT BatchNo,
       TranNo,
       WorkSource
FROM table1 WITH (NOLOCK)
WHERE BatchNo IN ('0000000420', '0000000421', '0000000422')
  AND PROCESSDATE = '20190206'
GROUP BY WorkSource,
         BATCHNO,
         TranNo;

Answer 1

你可以试试这个

create table #temp (WorkSource char(3), BatchNo char(3), TranNo int)
insert into #temp values
('012', '001', 1),
('012', '001', 1),
('012', '001', 2),
('012', '001', 3),
('012', '002', 1),
('012', '002', 2),
('012', '003', 3),
('013', '005', 1),
('013', '005', 2)

SELECT *, Dense_Rank() OVER(ORDER BY BatchNo, TranNo) AS Row_Number  
FROM #temp