我的问题与此链接中发布的问题非常相似 –
How to add sequence number for groups in a SQL query without temp tables
但是,我需要列举组的发生.最终输出如下:
- Record Group GroupSequence
- -------|---------|--------------
- 1 Chickens 1
- 2 Chickens 2
- 3 Cows 1
- 4 Horses 1
- 5 Horses 2
- 6 Horses 3
此外,这必须在Oracle sql中完成.有任何想法吗?
解决方法
也许是这样的:
- SELECT
- ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence1,RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence2,DENSE_RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence3,Table1.Group,Table1.Record
- FROM
- Table1
GroupSequence1,GroupSequence2和GroupSequence3将为您提供所需的输出.