SQL用值更新列的函数

Question

那些曾经帮助过我的人，我倾向于在我的日常工作中使用SAS9.4很多，但是有时我需要使用SQL Server

有一个输出表，我有2个变量(附加output.csv)输出表

ID, GROUP, DATE

表有830行:330有一个"c";集团150有一个"a";集团50人有一个"B">

其余300人分组为"TEMP">

SQL中的不知道如何通过编程计算出A+B+C的总量。目的是更新"TEMP"列，以确保有等量的"A"one_answers";B"每个合计250个(总数的剩余部分)

所以表的总数

330有一个"集团250人有"a";集团250有一个"B">

Answer 1

你想要按比例显示获得等量的"a";和"B".

所以，我们的想法是把A, B和Temp中的所有东西加起来，然后除以2。这是最终的群体规模。然后，您可以使用算术将Temp中的行分配给两个组:

select t.*,
(case when seqnum + a_cnt <= final_group_size then 'A' else 'B' end) as allocated_group
from (select t.*, row_number() over (order by newid()) as seqnum
from t
where group = 'Temp'
) t cross join
(select (cnt_a + cnt_b + cnt_temp) / 2 as final_group_size,
g.*
from (select sum(case when group = 'A' then 1 else 0 end) as cnt_a,
sum(case when group = 'B' then 1 else 0 end) as cnt_b,
sum(case when group = 'Temp' then 1 else 0 end) as cnt_temp
from t
) g
) g

SQL Server可以很容易地将其放入update:

with toupdate as (
select t.*,
(case when seqnum + a_cnt <= final_group_size then 'A' else 'B' end) as allocated_group
from (select t.*, row_number() over (order by newid()) as seqnum
from t
where group = 'Temp'
) t cross join
(select (cnt_a + cnt_b + cnt_temp) / 2 as final_group_size,
g.*
from (select sum(case when group = 'A' then 1 else 0 end) as cnt_a,
sum(case when group = 'B' then 1 else 0 end) as cnt_b,
sum(case when group = 'Temp' then 1 else 0 end) as cnt_temp
from t
) g
) g
)
update toupdate
set group = allocated_group;

Answer 2

我会选择top 250更新样式方法

update top (250) [TableName] set Group = 'A' where exists (Select * from [TableName] t2 where t2.id = [TableName].id order by newid()) and Group = 'Temp'
update top (250) [TableName] set Group = 'B' where exists (Select * from [TableName] t2 where t2.id = [TableName].id order by newid()) and Group = 'Temp'