更新时间:2023-01-29 16:41:23
如果您不介意某些表数据无法处理(取决于表大小,预计50%的行不能进行处理),这是一个解决方案:
If you don't mind around some of the table data cannot be processed (depends on table size, expect 50% row cannot be processed), here's a solution:
id name
1 Some
2 Body
3 Once
4 Told
5 Me
6 The
7 World
8 Is
9 Gonna
10 Roll
11 Me
12 I
13 Ain't
14 The
15 Shapest
16 Tool
17 In
18 The
19 Shed
20 She
21 was
22 looking
23 kind
24 of
25 dumb
26 with
27 her
28 finger
29 and
30 her
31 thumb
SELECT new_id, name FROM (
SELECT new_id, name FROM (
SELECT new_meme.id as new_id, original_meme.id as original_id, original_meme.name FROM meme original_meme
JOIN meme new_meme ON new_meme.id <> original_meme.id
ORDER BY RAND()
) layer1
GROUP BY layer1.new_id
) layer2 GROUP BY name
1 I
2 In
3 Gonna
4 Ain't
5 The
6 her
7 finger
8 Some
9 dumb
10 She
15 Me
16 with
17 Told
18 and
19 World
21 Roll
22 The
25 Tool
26 Shed
27 Is
28 Me
29 Sharpest
31 The
注意:您可能会发现查询非常慢,这是因为它两次连接了表,因此,如果数据大小为1000,则需要处理1000 * 1000.
Note: You might find the query very slow, this is because it join the table twice, so if data size is 1000, it will need to process 1000 * 1000.
您可以通过将ON new_meme.id <> original_meme.id
切换为ON new_meme.id BETWEEN original_meme.id - 5 AND original_meme.id +5
(可以更改5)来控制查询的速度,但是它将降低随机性,并且不适用于非数字ID
You can control the speed of the query by chaging ON new_meme.id <> original_meme.id
to ON new_meme.id BETWEEN original_meme.id - 5 AND original_meme.id +5
(the 5 can be change), but it will decrease randomness and not worked for non-numberical id