Secret Santa List
От | Lou Duchez |
---|---|
Тема | Secret Santa List |
Дата | |
Msg-id | 567A19E0.5070408@paprikash.com обсуждение исходный текст |
Ответы |
Re: Secret Santa List
Re: Secret Santa List Re: Secret Santa List |
Список | pgsql-general |
I have a company with four employees who participate in a Secret Santa program, where each buys a gift for an employee chosen at random. (For now, I do not mind if an employee ends up buying a gift for himself.) How can I make this work with an SQL statement? Here is my Secret Santa table: -- create table secretsanta (giver text, recipient text, primary key (giver)); insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'), ('Earl'); -- Here is the SQL statement I am using to populate the "recipient" column: -- update secretsanta set recipient = ( select giver from secretsanta s2 where not exists (select * from secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 ); -- The problem: every time I run this, a single name is chosen at random and used to populate all the rows. So all four rows will get a recipient of "Steve" or "Earl" or whatever single name is chosen at random. I suppose the problem is that the "exists" subquery does not re-evaluate for each record. How do I prevent this from happening? Can I use a "lateral" join of some kind, or somehow tell PostgreSQL to not be so optimized?
В списке pgsql-general по дате отправления: