SQL：查询根据组参数设置值

I need help writing a query to do the following on a table in my DB:

我需要帮助编写查询以在我的数据库中的表上执行以下操作:

Select all rows where the values in Column1 are the same
    If a 'Primary' has not been set in Column2 for any of them
        Set Column2 = 'Primary' for first row in the group 
    Else go on to the next group

Key points: I can only have one 'Primary' tag per group

要点:我每组只能有一个“主要”标签

Sample input:

Column1 | Column2 | 
ID1                 
ID1                       
ID1                     
ID2       Primary
ID2       
ID3       
ID3

Sample Result:

Column1 | Column2 | 
ID1       Primary         
ID1                       
ID1                     
ID2       Primary
ID2       
ID3       Primary   
ID3

Thank you!

2 个解决方案

#1

Use ROW_NUMER window function to find the first record per group then update only the records which doesnot have primary value in column2

使用ROW_NUMER窗口函数查找每组的第一条记录,然后仅更新第2列中没有主要值的记录

Since there is no column available to find the first record per group i have used (select null) in order by

由于没有列可用于按顺序查找每个组的第一条记录(选择null)

Sample data

CREATE TABLE #ee(Column1 VARCHAR(50),Column2 VARCHAR(50))

INSERT #ee
VALUES ('ID1',NULL),
       ('ID1',NULL),
       ('ID1',NULL),
       ('ID2','Primary'),
       ('ID2',NULL),
       ('ID3',NULL),
       ('ID3',NULL)

Update query:

;WITH cte
     AS (SELECT *,
                Row_number()OVER(partition BY column1 ORDER BY (SELECT NULL)) RN
         FROM   #ee a
         WHERE  NOT EXISTS (SELECT 1
                            FROM   #ee b
                            WHERE  a.Column1 = b.Column1
                                   AND b.Column2 = 'primary'))
UPDATE cte
SET    Column2 = 'primary'
WHERE  rn = 1

Result:

Column1 Column2
------- -------
ID1     primary
ID1     NULL
ID1     NULL
ID2     Primary
ID2     NULL
ID3     primary
ID3     NULL

#2

SELECT Column1  
     ,CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY [TargetColumn]) = 1 
          THEN  'Primary' 
          ELSE '' 
     END AS Column2
FROM TableName

TargetColumn will be the column that will decide in each group of Column1 which Value is the Primary Value.

TargetColumn将是将在Column1的每个组中决定哪个Value是主值的列。

#1