提问人：Thomas 提问时间：1/25/2023 最后编辑：Thomas 更新时间：1/25/2023 访问量：39
SQL - 使用特定的值列表随机化现有值
SQL - randomizing existing values with a specific list of values
问：
这个问题涉及 3 个表：
[person_attribute_type]
+--------------------------+-------------+
| person_attribute_type_id | foreign_key |
+--------------------------+-------------+
| 16 | 694 |
| 25 | 178 |
| 27 | 177 |
| 28 | 163 |
+--------------------------+-------------+
[concept_answer]
+------------+----------------+
| concept_id | answer_concept |
+------------+----------------+
| 163 | 342 |
| 163 | 343 |
| 163 | 251 |
| 163 | 253 |
| 163 | 320 |
| 163 | 322 |
| 163 | 408 |
| 177 | 408 |
| 177 | 254 |
| 177 | 255 |
| 177 | 256 |
| 177 | 344 |
| 177 | 257 |
| 178 | 229 |
| 178 | 345 |
| 178 | 346 |
| 178 | 347 |
| 178 | 348 |
| 694 | 692 |
| 694 | 685 |
| 694 | 689 |
| 694 | 690 |
| 694 | 687 |
| 694 | 684 |
| 694 | 693 |
| 694 | 686 |
| 694 | 688 |
| 694 | 691 |
+------------+----------------+
（concept_id：它与 person_attribute_type.foreign_key 中包含的值相同）
[person_attribute]
+--------------------------+-------+
| person_attribute_type_id | value |
+--------------------------+-------+
| 16 | 688 |
| 25 | 348 |
| 27 | 256 |
| 28 | 251 |
| 16 | 687 |
| 25 | 348 |
| 27 | 408 |
| 28 | 342 |
| 16 | 684 |
| 25 | 345 |
| 27 | 408 |
| 28 | 343 |
| 16 | 687 |
| 25 | 347 |
| 27 | 344 |
| 28 | 342 |
| 16 | 684 |
| 25 | 346 |
| 27 | 254 |
| 28 | 320 |
| 16 | 693 |
| 25 | 347 |
| 27 | 344 |
| 28 | 342 |
| 16 | 687 |
| 25 | 345 |
| 27 | 254 |
| 28 | 251 |
| 16 | 690 |
| 25 | 347 |
| 27 | 408 |
| 28 | 251 |
| 16 | 687 |
| 25 | 229 |
| 27 | 257 |
| 28 | 342 |
| 16 | 689 |
| 25 | 348 |
| 27 | 408 |
| 28 | 322 |
| 16 | 688 |
| 25 | 347 |
| 27 | 256 |
| 28 | 342 |
| 16 | 684 |
| 25 | 345 |
| 27 | 344 |
| 28 | 343 |
| 16 | 689 |
| 25 | 348 |
（值：与 concept_answer.answer_concept 中包含的值相同）
--
我想在其可接受的答案列表中使用 concept_answer.answer_concept 及其person_attribute_type_id随机化 person_attribute.value。
我希望这是可以理解的..
这就是我所做的：
-- Getting the variables for each line of person_attribute_type_id & foreign key
SELECT person_attribute_type_id, foreign_key INTO @pati1, @fk1 FROM person_attribute_type LIMIT 0,1;
SELECT person_attribute_type_id, foreign_key INTO @pati2, @fk2 FROM person_attribute_type LIMIT 1,1;
SELECT person_attribute_type_id, foreign_key INTO @pati3, @fk3 FROM person_attribute_type LIMIT 2,1;
SELECT person_attribute_type_id, foreign_key INTO @pati4, @fk4 FROM person_attribute_typeLIMIT 3,1;
-- Getting manually all the answers linked to the foreign_key
SET @answers1:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 0,1);
SET @answers2:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 1,1);
SET @answers3:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 2,1);
SET @answers4:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 3,1);
SET @answers5:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 4,1);
SET @answers6:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 5,1);
SET @answers7:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 6,1);
SET @answers8:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 7,1);
SET @answers9:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 8,1);
SET @answers10:=(select answer_concept FROM concept_answer WHERE concept_id = @fk1 LIMIT 9,1);
SET @2answers1:=(select answer_concept FROM concept_answer WHERE concept_id = @fk2 LIMIT 0,1);
SET @2answers2:=(select answer_concept FROM concept_answer WHERE concept_id = @fk2 LIMIT 1,1);
SET @2answers3:=(select answer_concept FROM concept_answer WHERE concept_id = @fk2 LIMIT 2,1);
SET @2answers4:=(select answer_concept FROM concept_answer WHERE concept_id = @fk2 LIMIT 3,1);
SET @2answers5:=(select answer_concept FROM concept_answer WHERE concept_id = @fk2 LIMIT 4,1);
SET @3answers1:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 0,1);
SET @3answers2:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 1,1);
SET @3answers3:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 2,1);
SET @3answers4:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 3,1);
SET @3answers5:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 4,1);
SET @3answers6:=(select answer_concept FROM concept_answer WHERE concept_id = @fk3 LIMIT 5,1);
SET @4answers1:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 0,1);
SET @4answers2:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 1,1);
SET @4answers3:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 2,1);
SET @4answers4:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 3,1);
SET @4answers5:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 4,1);
SET @4answers6:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 5,1);
SET @4answers7:=(select answer_concept FROM concept_answer WHERE concept_id = @fk4 LIMIT 6,1);
-- Randomize and update the values for person_attribute.value according to the "possible list of answers" for each concept_id
UPDATE person_attribute SET value = ELT(FLOOR(1 + RAND() * 10), @answers1, @answers2, @answers3, @answers4, @answers5, @answers6, @answers7, @answers8, @answers9, @answers10) WHERE person_attribute_type_id = @pati1;
UPDATE person_attribute SET value = ELT(FLOOR(1 + RAND() * 5), @2answers1, @2answers2, @2answers3, @2answers4, @2answers5) WHERE person_attribute_type_id = @pati2;
UPDATE person_attribute SET value = ELT(FLOOR(1 + RAND() * 6), @3answers1, @3answers2, @3answers3, @3answers4, @3answers5, @3answers6) WHERE person_attribute_type_id = @pati3;
UPDATE person_attribute SET value = ELT(FLOOR(1 + RAND() * 7), @4answers1, @4answers2, @4answers3, @4answers4, @4answers5, @4answers6, @4answers7) WHERE person_attribute_type_id = @pati4;
问题是：我确信有一种更简单的非手动和更短的方法可以达到相同的结果。有人知道吗？
多谢
答：
0赞
Sergey Kudriavtsev
1/25/2023
#1
在MySQL中实现您想要的最简单方法是。这为您提供了一个基于给定值集的随机值。从那里，您可以根据需要构建更复杂的查询。SELECT rand_val FROM tbl WHERE conditions ORDER BY RAND() LIMIT 1
应用于您的示例，它将是这样的
UPDATE person_attribute t
SET value =
(SELECT answer_concept
FROM person_attribute_type t2
LEFT JOIN concept_answer t3
ON t2.foreign_key=t3.concept_id
WHERE t2.person_attribute_type_id = t.person_attribute_type_id
ORDER BY RAND() LIMIT 1);
它应该替换整个 SQL 脚本并一次性更新所有行。
上一个：在查询中使用别名并使用它
下一个：如何为从数据库获取的变量赋值？
评论
SELECT rand_val FROM tbl WHERE conditions ORDER BY RAND() LIMIT 1