with cte2
as
(
SELECT NWMark,NWID,aid,ROW_NUMBER() Over(Partition by NWMark,nwid Order BY AID) AS rowindex
FROM [dbo].[AA_Daily] ad WITH(nolock)
join app on ad.AID=app.ID
WHERE Date>='2014-5-1' AND Date<'2014-5-5' AND ad.NWID=18 AND ad.RType=0 AND NWMark<>''
)
SELECT NWMark,NWID,aid,rowindex into #cte_AA_Daily FROM cte2 --WHERE rowindex=1
select * from #cte_AA_Daily
drop table #cte_AA_Daily
结果:
8155912967e54b27824e8c7573f0bfdd 18 CF76A1DD-DB9B-4C53-935D-00C2F5B272D4 1
8155912967e54b27824e8c7573f0bfdd 18 CF76A1DD-DB9B-4C53-935D-00C2F5B272D4 2
8155912967e54b27824e8c7573f0bfdd 18 CF76A1DD-DB9B-4C53-935D-00C2F5B272D4 3
8155912967e54b27824e8c7573f0bfdd 18 EEC7EB23-31F6-4049-8B05-01363452B667 4
8155912967e54b27824e8c7573f0bfdd 18 EEC7EB23-31F6-4049-8B05-01363452B667 5
8155912967e54b27824e8c7573f0bfdd 18 9E804B30-A9AA-4DD4-82CA-0187D07604C9 6
8155912967e54b27824e8c7573f0bfdd 18 9E804B30-A9AA-4DD4-82CA-0187D07604C9 7
8155912967e54b27824e8c7573f0bfdd 18 9E804B30-A9AA-4DD4-82CA-0187D07604C9 8
8155912967e54b27824e8c7573f0bfdd 18 9E804B30-A9AA-4DD4-82CA-0187D07604C9 9
8155912967e54b27824e8c7573f0bfdd 18 E2224065-71D9-4AAA-BAF0-022F0EBF947E 10
8155912967e54b27824e8c7573f0bfdd 18 E2224065-71D9-4AAA-BAF0-022F0EBF947E 11
8155912967e54b27824e8c7573f0bfdd 18 E2224065-71D9-4AAA-BAF0-022F0EBF947E 12
8155912967e54b27824e8c7573f0bfdd 18 A335B2D7-C133-42CC-B942-035E283BEEC8 13
8155912967e54b27824e8c7573f0bfdd 18 A335B2D7-C133-42CC-B942-035E283BEEC8 14
row_number() over (partition by NWMark,nwid order by AID)
row_number() 顺序号码, 也就是 行号, 比如 1,2,3,4,5 这样的顺序。
over 语法需要,必须的。 partition by NWMark,nwid 是按照 NWMark,nwid 分区。 也就是 如果有 不同的 NWMark,nwid ,这个 序号又重新从1开始计算。
order by AID 是 排序方式, 也就是 最小的 AID, row_number() 是1,然后随着 AID, row_number() 不断递增。