sql - 如何通过其序列号获得唯一记录

Question

我有一张表，其中包含以下数据：

Name        Code    Sequence    AutoIncrementId
------------------------------------------------
Prashant    14323     1             11
Prashant    14323     1             12
Prashant    14323     1             13
Prashant    14323     1             14
Nishant     11323     2             11
Nishant     11323     2             12
Nishant     11323     2             13
Nishant     11323     2             14
Sushant     13223     3             11
Sushant     13223     3             12
Sushant     13223     3             13
Sushant     13223     3             14
Jishant     14223     4             11
Jishant     14223     4             12
Jishant     14223     4             13
Jishant     14223     4             14

预期输出为：

Name      Code      Sequence    AutoIncrementId
------------------------------------------------
Prashant  14323        1             11
Nishant   11323        2             12
Sushant   13223        3             13
Jishant   14223        4             14

生成数据的示例查询：

CREATE TABLE #temp
(
Name NVARCHAR(200),
Code NVARCHAR(200),
Sequence NVARCHAR(200),
AutoIncrementId NVARCHAR(200)
)

INSERT INTO #temp VALUES ('Prashant',14323,1,11)
INSERT INTO #temp VALUES ('Prashant',14323,1,12)
INSERT INTO #temp VALUES ('Prashant',14323,1,13)
INSERT INTO #temp VALUES ('Prashant',14323,1,14)

INSERT INTO #temp VALUES ('Nishant',11323,2,11)
INSERT INTO #temp VALUES ('Nishant',11323,2,12)
INSERT INTO #temp VALUES ('Nishant',11323,2,13)
INSERT INTO #temp VALUES ('Nishant',11323,2,14)

INSERT INTO #temp VALUES ('Sushant',13223,3,11)
INSERT INTO #temp VALUES ('Sushant',13223,3,12)
INSERT INTO #temp VALUES ('Sushant',13223,3,13)
INSERT INTO #temp VALUES ('Sushant',13223,3,14)

INSERT INTO #temp VALUES ('Jishant',14223,4,11)
INSERT INTO #temp VALUES ('Jishant',14223,4,12)
INSERT INTO #temp VALUES ('Jishant',14223,4,13)
INSERT INTO #temp VALUES ('Jishant',14223,4,14)

我尝试过的查询：

SELECT ROW_NUMBER() OVER(ORDER BY AutoIncrementId,Sequence) as RN,* 
FROM #temp ORDER BY RN

标签： sqlsql-server

Answer 1

从您的示例数据中，您可以尝试使用distinct

SELECT distinct Namem,Code,Sequence,AutoIncrementId
FROM #temp