Question

我的表格A有几栏。我愿向表中所列各行提供最新情况。每一行头两栏随机从表B头两栏中选取。然而,我不能使用类似的东西。

update TableA
    set col1 = (select top 1 col1 from TableB order by newid()),
        col2 = (select top 1 col2 from TableB order by newid())

由于我需要表B中的两栏是随机选择的同一行。我怎么能写一个问询,以确保表B的两栏来自同一行?

Answer 1

一种很好的办法是将你的随机值写成一个临时表格,因为随机价值功能的点是,它要么每当你称之为它时(产生为<代码><1<>>>>>/代码>和<代码>>选择的不同行号——<2><>>>>>>>>>>>>,要么将某一种种子的相同价值(这将使得在<编码>>TableA<>>>>>>>/code>上的所有行文中只有一个随机选择的同值。

下面的代码假设,在<代码>TablekouA上,你有一个识别栏id,但你可以轻易修改。

-- make temporary table to use row numbers for TableA
select id, row_number() over (order by $/0) as [row] into #a from TableA;
-- make enough copies of TableB to have same number of rows as TableA
with n as
(
    select distinct n = number
           from [master]..spt_values
           where number between 1 and floor((select count(*) from TableA) / (select count(*) from TableB))
)
select b.col1, b.col2, row_number() over (order by newid()) as [row]
into #TableBRandom
from (
    select col1, col2 from TableB, n
    union all
    select top ((select count(*) from TableA) % (select count(*) from TableB)) col1, col2 from TableB
) b
-- update TableA
update TableA
    set col1 = (select top 1 col1 from #TableBRandom where [row] = (select [row] from #a where #a.id = TableA.id)),
        col2 = (select top 1 col2 from #TableBRandom where [row] = (select [row] from #a where #a.id = TableA.id))

友情链接