具有随机查找值的 SQL 更新行列 [英] SQL Update row column with random lookup value

问题描述

我正在尝试更新潜在客户表以从查找表中随机分配一个人.这是通用架构:

I am trying to update a lead table to assign a random person from a lookup table. Here is the generic schema:

TableA (header),
ID int,
name varchar (30)

TableB (detail),
ID int,
fkTableA int, (foreign key to TableA.ID)
recordOwner varchar(30) null
other detail colums..

TableC (owners),
ID int,
fkTableA int (foreign key to TableA.ID)
name varchar(30)

TableA 有 10 个条目，每个类型的销售线索池对应一个条目.TableB 在 TableA 中的每一行都有数千个条目.我想从 TableC 分配正确的 recordOwners 到每个行(或尽可能接近).TableC 将在 tableA 中的每一行包含一个条目或最多 10 个条目.

TableA has 10 entries, one for each type of sales lead pool. TableB has thousands of entries for each row in TableA. I want to assign the correct recordOwners from TableC to and even number of rows each (or as close as I can). TableC will have anywhere from one entry for each row in tableA or up to 10.

这可以在一个语句中完成吗?不必如此.我似乎无法找出速度的最佳方法.任何想法或样本表示赞赏.

Can this be done in one statement? It doesn't have to be. I can't seem to figure out the best approach for speed. Any thoughts or samples are appreciated.

更新:
TableA 与 TableC 有一对多的关系.对于TableA的每条记录，TableC至少会有一行，代表一个所有者，需要分配给TableB中的一行.

Updated:
TableA has a 1 to many relation ship with TableC. For every record of TableA, TableC will have at least one row, which represents an owner that will need to be assigned to a row in TableB.

TableA
int  name
1    LeadSourceOne
2    LeadSourceTwo

TableC
int(id) int(fkTableA) varchar(name)
1       1             Tom
2       1             Bob
3       2             Timmy
4       2             John
5       2             Steve
6       2             Bill

TableB initial data
int(id) int(fkTableA) varchar(recordOwner) (other detail columns)
1       1             NULL                 ....
2       1             NULL                 ....
3       1             NULL                 ....
4       2             NULL                 ....
5       2             NULL                 ....
6       2             NULL                 ....
7       2             NULL                 ....
8       2             NULL                 ....
9       2             NULL                 ....

TableB end result
int(id) int(fkTableA) varchar(recordOwner) (other detail columns)
1       1             TOM                  ....
2       1             BOB                  ....
3       1             TOM                  ....
4       2             TIMMY                ....
5       2             JOHN                 ....
6       2             STEVE                ....
7       2             BILL                 ....
8       2             TIMMY                ....
9       2             BILL                 ....

基本上，我需要根据与 tableA 的关系将 tableC 中的记录随机分配到 tableB.

Basically I need to randomly assign a record from tableC to tableB based on the relationship to tableA.

推荐答案

我最终循环并根据我拥有的所有者数量更新了 x% 的详细记录.最终结果是这样的:

I ended up looping thru and updating x percent of the detail records based on how many owners I had. The end result is something like this:

create table #tb_owners(userId varchar(30), processed bit)

    insert into #tb_owners(
        userId, 
        processed)
    select userId = name, 
        processed = 0
    from tableC 
    where fkTableA = 1

    select @percentUpdate = cast(100 / count(*) as numeric(8,2))
    from #tb_owners

    while exists(select 1 from #tb_owners o where o.processed = 0)
        begin
            select top 1 
                @userFullName = o.name
            from #tb_owners o
            where o.processed = 0
            order by newId()


            update tableB
            set recordOwner = @userFullName
            from tableB ptbpd
                inner join (
                            select top (@percentUpdate) percent 
                                id
                            from tableB
                            where recordOwner is null
                            order by newId()
                    ) nd on (ptbpd.id = nd.id)


            update #tb_owners
            set processed = 1
            where userId = @oUserId
        end

    --there may be some left over, set to last person used
    update tableB
    set recordOwner = @userFullName
    from tableB
    where ptbpd.recordOwner is null

这篇关于具有随机查找值的 SQL 更新行列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！