SQL更新列取决于同一列中的其他值

I have a table similar to this:

我有一个类似于这样的表:

Index    Name       Type
--------------------------------
1        'Apple'   'Fruit'
2        'Carrot'  'Vegetable'
3        'Orange'  'Fruit'
3        'Mango'   'Fruit'
4        'Potato'  'Vegetable'

and would like to change it to this:

并希望将其更改为:

Index    Name       Type
--------------------------------
1        'Apple'   'Fruit 1'
2        'Carrot'  'Vegetable 1'
3        'Orange'  'Fruit 2'
3        'Mango'   'Fruit 3'
4        'Potato'  'Vegetable 2'

Any chance to do this in a smart update query (= without cursors)?

有没有机会在智能更新查询中执行此操作(=没有游标)?

4 个解决方案

#1

You can run update with join to get row_number() within [type] group for each row and then concatenate this values with [type] using [index] as glue column:

您可以使用join运行update以在每个行的[type]组中获取row_number(),然后使用[index]作为glue列将此值与[type]连接:

update t1 set t1.[type] = t1.[type] + ' ' + cast(t2.[rn] as varchar(3))
from [tbl] t1
join ( select [index]
            , row_number() over (partition by [type] order by [index]) as [rn]
       from [tbl]
     ) t2 on t1.[index] = t2.[index]

SQLFiddle

#2

Suppose that your table has a Primary Key called ID then you can run the following:

假设您的表有一个名为ID的主键,那么您可以运行以下命令:

update fruits
set Type = newType
from
(
select f.id
       ,f.[index] 
       ,f.Name 
       ,f.[Type] 
       ,Type + ' '+ cast((select COUNT(*) 
                          from fruits 
                          where Type = f.Type 
                          and Fruits.id <= f.id) as varchar(10)) as newType
    from fruits f
) t
where t.id = fruits.id

#3

SELECT Index ,Name, Type, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Index)AS RowNum
INTO #temp
FROM table_name

UPDATE #temp
SET Type = Type + ' ' + CAST(RowNum AS NVARCHAR(15))

UPDATE table_name
SET Type = t2.Type
FROM table_name t1
JOIN #temp t2 ON t2.Index = t1.Index

#4

You can use the fact that you can update cte:

您可以使用以下事实:您可以更新cte:

with cte as(select type, row_number() over(partition by type order by index) rn from table)
update cte set type = type + ' ' + cast(rn as varchar(10))

#1