为什么我的SQL语句有时不返回结果？

Question

下表如下：

​

​

我们有以下SQL语句，它返回一个随机行：

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2    
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null 
ORDER BY r1.id ASC 
LIMIT 1

虽然这样做很好，但是当我们需要按稀缺性随机化时，因此需要将上面的内容改为下面的内容：

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2 
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1

它有时根本不返回任何行，具有惊人的规律性(我可以说，十分之一)。

这是什么原因？我怎样才能防止或解决它呢？

Answer 1

您的查询是在ID范围内选择一个随机数，然后在表中查找符合条件的下一行。如果后面的行都没有符合条件的随机ID，则不会得到任何信息。在选择随机ID时，还需要包括这些条件。

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT RAND() * MAX(id) AS id
    FROM items_permanent
    WHERE unlocks_prior is Null AND rarity = ? 
) AS r2 ON r1.id >= r2.id
WHERE r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1

Answer 2

我看到的是您在下面添加到第二个SQL中。

AND r1.rarity = ? 

以图像中显示的数据为例，假设您有ID 1~ 29，如果您有r1，稀缺性=2，r2，ID滚动为28 (因为ID28-29不稀罕=2)，您将一无所获。

这应该能够通过更改下面的语句来修复

SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id

转到

SELECT (RAND() * (SELECT MAX(id) FROM items_permanent WHERE rarity = ?)) AS id