主页 / 问题 / 1018965
Accepted
XelaNimed
Asked:2020-08-30 20:17:16 +0000 UTC2020-08-30 20:17:16 +0000 UTC

如何通过分组序号更新列？

  • 772

如何构建查询以按分组中的序号更新列？例如，有下表：

+----+------+------+----------+
| Id | Col1 | Col2 |   Col3   |
+----+------+------+----------+
|  1 | aaa  | bbb  | abcdef   |
|  2 | ccc  | ddd  | ghijklmn |
|  3 | eee  | fff  | abcdef   |
|  4 | ggg  | hhh  | ghijklmn |
|  5 | iii  | jjj  | ghijklmn |
|  6 | kkk  | lll  | abcdef   |
+----+------+------+----------+

可以看到，Col3列中有重复值。如何构建一个 SQL 查询来更新表，以便在其执行后，组的序号在列中，Col1还是Col2无关紧要？排序顺序无关紧要。目标是为每个组设置一个唯一的数字。

+----+------+------+----------+
| Id | Col1 | Col2 |   Col3   |
+----+------+------+----------+
|  1 | aaa  | 1    | abcdef   |
|  2 | ccc  | 2    | ghijklmn |
|  3 | eee  | 1    | abcdef   |
|  4 | ggg  | 2    | ghijklmn |
|  5 | iii  | 2    | ghijklmn |
|  6 | kkk  | 1    | abcdef   |
+----+------+------+----------+
sql

2 个回答

  1. Best Answer
    DECLARE @T TABLE (Id int, Col1 nvarchar(50), Col2 nvarchar(50), Col3 nvarchar(50))

INSERT @T (Id, Col1, Col2, Col3)
VALUES (1 , N'aaa'  , N'bbb'  , N'abcdef')
      ,(2 , N'ccc'  , N'ddd'  , N'ghijklmn')
      ,(3 , N'eee'  , N'fff'  , N'abcdef')
      ,(4 , N'ggg'  , N'hhh'  , N'ghijklmn')
      ,(5 , N'iii'  , N'jjj'  , N'ghijklmn')
      ,(6 , N'kkk'  , N'lll'  , N'abcdef')

;WITH [CTE]
AS
(
    SELECT Id
          ,DENSE_RANK() OVER(ORDER BY Col3) AS [NewCol2]
      FROM @T
)
UPDATE @T
   SET Col2 = [NewCol2]
  FROM [CTE]
 WHERE [@T].[Id] = [CTE].[Id]

SELECT *
  FROM @T
    • 3

  2. MS SQL（选项之一）：

    declare @t table (Id int, Col1 varchar(10),Col2 varchar(10), Col3 varchar(20))
insert into @t (Id, Col1, Col2, Col3)
values 
(  1 , 'aaa'  , 'bbb', 'abcdef'),
(  2 , 'ccc'  , 'ddd'  , 'ghijklmn' ),
(  3 , 'eee'  , 'fff'  , 'abcdef'   ),
(  4 , 'ggg'  , 'hhh'  , 'ghijklmn' ),
(  5 , 'iii'  , 'jjj'  , 'ghijklmn' ),
(  6 , 'kkk'  , 'lll'  , 'abcdef');
select ROW_NUMBER() OVER(ORDER BY b.Col3) as N, b.Col3 from (select a.Col3 from @t a group by a.Col3) b

select 
    t.id as Id,
    t.Col1,
    N.N,
    t.Col3
from @t t  INNER JOIN 
    (select ROW_NUMBER() OVER(ORDER BY b.Col3) as N, b.Col3 from (select a.Col3 from @t a group by a.Col3) b) N
    ON t.Col3 = N.Col3
order by t.Id;
    • 0

相关问题