我想得到sql数据库中值的第一次、第二次和第n次？

Question

我想为每一行中的属性计数属性值出现的位置：

id  | attribute      |Occurence

1  | ICT              1   |the first time of ICT
2  | ENGINEERING      1   |the first time of ENGINEERING      
3  | ICT              2   |the second time of ICT
4  | ENGINEERING      2   |the second time of  ENGINEERING          
5  | ENGINEERING      3   |the Third time of  ENGINEERING 

Answer 1

您可以使用ROW_NUMBER对其进行计数。这里将更详细地介绍这一点。

CREATE TABLE Table1
    ([id] int, [attribute] varchar(11))
;

INSERT INTO Table1
    ([id], [attribute])
VALUES
    (1, 'ICT'),
    (2, 'ENGINEERING'),
    (3, 'ICT'),
    (4, 'ENGINEERING'),
    (5, 'ENGINEERING')
;


SELECT
  id,attribute
  ,ROW_NUMBER()OVER(PARTITION BY Attribute ORDER BY id) as Occurence
FROM
  Table1
ORDER BY id;

产出：

id  attribute   Occurence
1   ICT         1
2   ENGINEERING 1
3   ICT         2
4   ENGINEERING 2
5   ENGINEERING 3

dbfiddle 这里