Postgresql 随机行分配

Question

我有2张 table

Student

|---------------------|------------------|
|         id          |     studentname  |
|---------------------|------------------|
|          1          |     studentA     |
|---------------------|------------------|
|          2          |     studentB     |
|---------------------|------------------|
|          3          |     studentC     |
|---------------------|------------------|
|          4          |     studentD     |
|---------------------|------------------|  

Chair

|---------------------|------------------|
|      id             |     chairname    |
|---------------------|------------------|
|          1          |     chairA       |
|---------------------|------------------|
|          2          |     chairB       |
|---------------------|------------------|
|          3          |     chairC       |
|---------------------|------------------|

我需要一个查询来随机分配一个学生到 postgresql 中的椅子。请注意，椅子可以比学生多，反之亦然。如果学生多于椅子，则未分配的学生在椅子上的值为空。

对于上面的表格，我们应该得到这样的结果

|---------------------|------------------|
|      studentname    |     chairname    |
|---------------------|------------------|
|          studentA   |     chairC       |
|---------------------|------------------|
|          studentB   |     chairA       |
|---------------------|------------------|
|          studentC   |     chairB       |
|---------------------|------------------|
|          studentD   |     NULL         |
|---------------------|------------------|

或

|---------------------|------------------|
|      studentname    |     chairname    |
|---------------------|------------------|
|          studentA   |     chairA       |
|---------------------|------------------|
|          studentB   |     chairC       |
|---------------------|------------------|
|          studentC   |     NULL         |
|---------------------|------------------|
|          studentD   |     chairB       |
|---------------------|------------------|

知道如何在 postgresql 中完成此操作吗？

Answer 1

您可以尝试使用随机排序的 ROW_NUMBER 将学生 ID 分配给不同的椅子:

WITH cte1 AS (
    SELECT chairname, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
    FROM Chair
),
cte2 AS (
    SELECT studentname, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM Student
)

SELECT
    s.studentname,
    c.chairname
FROM cte2 s
LEFT JOIN cte1 c
    ON s.rn = c.rn;

Demo

请注意，我实际上在两个表中生成了一个行号序列，包括 Student 表。这是为了确保我们始终比较两个表之间匹配的行号值。

编辑:将 LEFT JOIN 替换为 FULL OUTER JOIN 以通常处理学生人数超过椅子的情况，反之亦然。