mysql - 如何使用 ORDER BY RAND() 将同一张表与自身连接起来？

Question

我想获得一个表的 id 与其自身的随机组合。

SELECT id FROM t1
SELECT id as id2 FROM t1 ORDER BY RAND()
SELECT id as id3 FROM t1 ORDER BY RAND()

我如何JOIN这些查询来获取

SELECT id,id2,id3

1    random_id    random_id
2    random_id    random_id
3    random_id    random_id
4    random_id    random_id
5    random_id    random_id

换句话说，JOIN 将这三个 SELECT 并排放置的意义何在。

创建一个唯一的组合是有益的，但是使用上面的查询ORDER BY RAND()可以重复相同的id到id2和 id3。前者是理想的，但后者对我来说已经足够了。

Answer 1

如果您使用的是 MySQL 8+，则 ROW_NUMBER 可能适用于此:

WITH cte AS (
    SELECT id,
        ROW_NUMBER() OVER (ORDER BY id) rn1,
        ROW_NUMBER() OVER (ORDER BY RAND(UNIX_TIMESTAMP())) rn2,
        ROW_NUMBER() OVER (ORDER BY RAND(UNIX_TIMESTAMP()+1)) rn3
    FROM t1
)

SELECT
    t1.id,
    t2.id AS id2,
    t3.id AS id3
FROM cte t1
INNER JOIN cte t2
    ON t1.rn1 = t2.rn2
INNER JOIN cte t3
    ON t1.rn1 = t3.rn3;

下面的演示来自一个示例表，其中包含从 1 到 10 的 id 值。

Demo