如何实现多条件下无重复记录的随机SQL查询？

针对需求，我们可以通过横向连接（LATERAL JOIN）或窗口函数+CTE实现，既避免大量UNION，又能保证每个条件各返回一条不重复的随机记录。以下是具体方案：

示例数据表 #

假设你的表名为your_table，数据如下：

需要获取的三条记录要求：

1. 满足value BETWEEN 9 AND 10的随机记录
2. 满足value BETWEEN 9 AND 11的随机记录（不能与第一条重复）
3. 满足value BETWEEN 10 AND 15的随机记录（不能与前两条重复）

方案一：使用LATERAL JOIN（推荐，适用于MySQL 8.0.14+/PostgreSQL等） #

横向连接可以逐步筛选记录，自动排除已选中的id，从根源避免重复：

-- 按条件依次筛选，每行返回一个条件的记录
WITH selected_records AS (
    -- 筛选条件A的随机记录
    SELECT id, value, '条件A（9-10）' AS condition
    FROM (SELECT id, value FROM your_table WHERE value BETWEEN 9 AND 10 ORDER BY RAND() LIMIT 1) AS a
    
    UNION ALL
    
    -- 筛选条件B的随机记录，排除已选的条件A的id
    SELECT b.id, b.value, '条件B（9-11）' AS condition
    FROM (SELECT id, value FROM your_table WHERE value BETWEEN 9 AND 10 ORDER BY RAND() LIMIT 1) AS a
    LATERAL (SELECT id, value FROM your_table WHERE value BETWEEN 9 AND 11 AND id != a.id ORDER BY RAND() LIMIT 1) AS b
    
    UNION ALL
    
    -- 筛选条件C的随机记录，排除已选的条件A、B的id
    SELECT c.id, c.value, '条件C（10-15）' AS condition
    FROM (SELECT id, value FROM your_table WHERE value BETWEEN 9 AND 10 ORDER BY RAND() LIMIT 1) AS a
    LATERAL (SELECT id, value FROM your_table WHERE value BETWEEN 9 AND 11 AND id != a.id ORDER BY RAND() LIMIT 1) AS b
    LATERAL (SELECT id, value FROM your_table WHERE value BETWEEN 10 AND 15 AND id NOT IN (a.id, b.id) ORDER BY RAND() LIMIT 1) AS c
)
SELECT * FROM selected_records;

方案二：使用窗口函数+CTE（通用SQL标准，支持多数现代数据库） #

通过窗口函数生成随机排名，分步筛选排除已选记录：

WITH candidate_data AS (
    SELECT
        id,
        value,
        RAND() AS rand_score, -- 生成全局随机数用于排序
        -- 标记每条记录满足的条件
        CASE WHEN value BETWEEN 9 AND 10 THEN 1 ELSE 0 END AS meets_a,
        CASE WHEN value BETWEEN 9 AND 11 THEN 1 ELSE 0 END AS meets_b,
        CASE WHEN value BETWEEN 10 AND 15 THEN 1 ELSE 0 END AS meets_c
    FROM your_table
),
-- 筛选条件A的随机记录
cond_a AS (SELECT id, value FROM candidate_data WHERE meets_a = 1 ORDER BY rand_score LIMIT 1),
-- 筛选条件B的随机记录，排除已选的条件A的id
cond_b AS (SELECT id, value FROM candidate_data WHERE meets_b = 1 AND id NOT IN (SELECT id FROM cond_a) ORDER BY rand_score LIMIT 1),
-- 筛选条件C的随机记录，排除已选的条件A、B的id
cond_c AS (SELECT id, value FROM candidate_data WHERE meets_c = 1 AND id NOT IN (SELECT id FROM cond_a UNION SELECT id FROM cond_b) ORDER BY rand_score LIMIT 1)

-- 合并结果
SELECT id, value, '条件A（9-10）' AS condition FROM cond_a
UNION ALL
SELECT id, value, '条件B（9-11）' AS condition FROM cond_b
UNION ALL
SELECT id, value, '条件C（10-15）' AS condition FROM cond_c;

方案优势 #

• 避免大量重复UNION子查询，逻辑清晰易维护
• 确保每个条件都能返回符合要求的记录
• 通过RAND()保证随机性，同时通过id排除机制彻底避免重复

内容的提问来源于stack exchange，提问作者Woton Sampaio