SQL整合数据组 [英] SQL Consolidating groups of data

问题描述

我在表格中填入以下数据

I have the following data in a table

col1      col2              col3
276328  | 999999999999  |   664116
927356  | 999999999999  |   664140
927356  | 999999999999  |   664140
927356  | 999999999999  |   664159
927379  | 999999999999  |   664172

code：

code:

create table #table (col1 bigint, col2 bigint, col3 bigint)
insert into #table values(276328, 999999999999, 664116)
insert into #table values(927356, 999999999999, 664140)
insert into #table values(927356, 999999999999, 664140)
insert into #table values(927356, 999999999999, 664159)
insert into #table values(927379, 999999999999, 664172)

我需要将Col2更新为Col3中的值，同时对col1进行分组并选择Min（col1）适用于该小组。

I need to update Col2 to the value in Col3 while grouping col1 and selecting the Min (col1) to apply to that sub group.

276328和927379在他们自己的群组中，但927356是需要分配给col2的最低col3编号的群组

276328 and 927379 are on their own group but 927356 is a group that needs to have the lowest col3 number assigned to col2

因此，上面更新的表应该如下所示：

So, the table above having been updated should look like this:

col1        | col2      | col3
276328      | 664116    | 664116
927356      | 664140    | 664140
927356      | 664140    | 664140
927356      | 664140    | 664159
927379      | 664172    | 664172

我有2m +行更新，所以它必须是批量更新而不是循环。

I have 2m+ rows to update so it has to be a bulk update not a loop.

如何编写SQL来更新表格？

How would I write the SQL to update the table?

推荐答案

declare @myTable table (col1 bigint, col2 bigint, col3 bigint)
insert @myTable
select 276328  , 999999999999  ,   664116
union select 927356  , 999999999999  ,   664140
union select 927356  , 999999999999  ,   664140
union select 927356  , 999999999999  ,   664159
union select 927379  , 999999999999  ,   664172

update a
set a.col2 = b.col3
from @myTable a
inner join 
(
    select col1, MIN(col3) col3
    from @myTable 
    group by col1
) b
on a.col1 = b.col1


select * from @myTable

这篇关于SQL整合数据组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！