Jak mogę przypisać różne losowe wartości do każdego wiersza w instrukcji SELECT?

Proszę spojrzeć na ten kod:

create table #t1(
  id int identity (1,1),
  val varchar(10)
);


insert into #t1 values ('a');
insert into #t1 values ('b');
insert into #t1 values ('c');
insert into #t1 values ('d');

Teraz, ilekroć to wykonasz

select *, 
    ( select top 1 val from #t1 order by NEWID()) rnd 
from #t1 order by 1;

otrzymasz wynik, w którym wszystkie wiersze mają tę samą losową wartość. na przykład

id          val        rnd
----------- ---------- ----------
1           a          b
2           b          b
3           c          b
4           d          b

Znam sposób, w jaki kursor używa pętli do rzucania wierszami i uzyskiwania różnych losowych wartości, ale to nie jest wydajne.

Sprytnym rozwiązaniem tego jest

select t1.id, t1.val, t2.val
from #t1 t1
    join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd 

Ale uprościłem zapytanie. Prawdziwe zapytanie wygląda bardziej

select *, 
    ( select top 1 val from t2 where t2.x <> t1.y order by NEWID()) rnd 
from t1 order by 1;

a proste rozwiązanie nie pasuje. Szukam sposobu wymuszenia wielokrotnej oceny

( select top 1 val from #t1 order by NEWID()) rnd 

bez użycia kursorów.

Edycja: Poszukiwany wynik:

może 1 połączenie

id          val        rnd
----------- ---------- ----------
1           a          c
2           b          c
3           c          b
4           d          a

i drugie połączenie

id          val        rnd
----------- ---------- ----------
1           a          a
2           b          d
3           c          d
4           d          b

Wartość każdego wiersza powinna być wartością losową niezależną od innych wierszy

Oto wersja kursora kodu:

CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));

DECLARE @id INT
DECLARE @val VARCHAR(10)
DECLARE c CURSOR FOR
SELECT id, val
FROM #t1
OPEN c
FETCH NEXT FROM c INTO @id, @val
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #res
    SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd 
    FETCH NEXT FROM c INTO @id, @val
END
CLOSE c
DEALLOCATE c

SELECT * FROM #res

W miarę możliwości podzapytanie jest oceniane raz. Niestety nie pamiętam, jak nazywa się „funkcja” (składanie?).

To samo dotyczy funkcji GETDATE i RAND. NEWID jest oceniany wiersz po rzędzie, ponieważ jest on z natury losową wartością i nigdy nie powinien generować tej samej wartości dwa razy.

Typowe techniki to użycie NEWID jako danych wejściowych do CHECKSUM lub jako źródła do RAND

Dla losowych wartości w rzędzie:

SELECT
   co1l, col2,
   ABS(CHECKSUM(NEWID())) AS Random1,
   RAND(CHECKSUM(NEWID())) AS Random2
FROM
   MyTable

Jeśli chcesz losowe zamówienie:

SELECT
   co1l, col2
FROM
   MyTable
ORDER BY
   NEWID()

Jeśli chcesz także losowe zamówienie z rzędem. Tutaj zamówienie ActualOrder jest zachowywane niezależnie od kolejności zestawu wyników

SELECT
   id, val,
   ROWNUMBER() OVER (ORDER BY id) AS id
FROM
   #t1
ORDER BY
   NEWID()

Edytować:

W takim przypadku możemy określić wymaganie jako:

1. zwraca dowolną wartość losową z zestawu dla każdego wiersza w zestawie
2. wartość losowa będzie inna niż rzeczywista wartość w dowolnym wierszu

Różni się to od tego, co zaoferowałem powyżej, które po prostu zamawia wiersze na różne sposoby

Zastanowiłbym się, CROSS APPLY. Klauzula WHERE wymusza ocenę rząd po rzędzie i pozwala uniknąć problemu „składania” i zapewnia, że ​​val i rnd są zawsze różne. APLIKACJA KRZYŻOWA może również całkiem dobrze skalować

SELECT
   id, val, R.rnd
FROM
   #t1 t1
   CROSS APPLY
   (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R
ORDER BY
   id