获取名称列表并随机配对它们(SQL)

Question

我正在尝试做一些我认为相当容易的事情，现在我即将把头从桌子上摔下来！

我有一个名称列表作为数组，我想从该列表创建随机对。

所以我有

SELECT * FROM (
VALUES
('Angie'),
('Bob'),
('Meg'),
('Colin'),
('Debbie'),
('Eddie'),
('Fiona'),
('Gary'),
('Harriet'),
('Ian'),
('Julie'),
('Kevin'),
('Mary'),
('Noah'),
('Olivia')
) AS t (name)enter code here

我想找回类似的东西

Name 1  Name2
1. Olivia Debbie
2. Gary   Harriet
3. Bob    Mary
4. Noah   Colin
5. Ian    Fiona
6. Kevin  Mary
7. Julie  Eddie
8. Angie  NULL

有没有办法做到这一点？

Answer 1

使用row_number()和聚合：

select max(case when mod(seqnum, 2) = 1 then name end) as name1,
max(case when mod(seqnum, 2) = 0 then name end) as name2       
from (select name, row_number() over (order by random()) - 1 as seqnum
from t
) t
group by floor(seqnum / 2);