MySQL LIMIT 最多为某个数字 - 取决于先前的子查询

Question

我正在尝试构建一个 SQL 查询，该查询从不同的表中随机选择条目，最多可达一定数量。

假设我有 3 个表 A、B 和 C。我想从 A、B 和 C 中总共选择 10 行。

现在我想从A中随机选择2个条目，从B中最多选择6个条目(取决于从A中检索到的条目数。如果A没有任何行，我想从B中获取6行。如果A返回1 行，我想从 B 获取 5 行，依此类推..)。然后我想用 C 中随机选择的行填充剩余的条目(0 到 10 之间，取决于之前的子查询)。到目前为止我已经尝试过:

select * from (
(SELECT * FROM A  ORDER BY RAND() LIMIT 2)
UNION
(SELECT * FROM B ORDER BY RAND())
limit 4) a

不幸的是，一旦我联合两个子查询，第二个查询的随机顺序就会丢失，并且我总是从 B 检索相同的前 4 行。仅使用 SQL 就可以做到这一点，还是必须执行多个查询并以编程方式联合它们？

致以诚挚的问候

Answer 1

您可以使用如下查询来完成此操作:

SELECT * FROM (
    SELECT * FROM (
        SELECT ab.* FROM (
            SELECT * FROM ( SELECT 1 as level,a.* FROM table_a AS a ORDER BY RAND() LIMIT 2) atmp
            UNION
            SELECT * FROM ( SELECT 2 as level ,b.* FROM table_b AS b ORDER BY RAND() LIMIT 6 ) btmp
        ) AS ab ORDER BY ab.level LIMIT 6
    ) AS totab
UNION ALL
    SELECT * FROM ( SELECT 3 as level,c.* FROM table_c AS c ORDER BY RAND() LIMIT 10) ctmp
) AS totabc ORDER BY totabc.level LIMIT 10;

示例表

mysql> select * from table_a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0,00 sec)

mysql> select * from table_b;
+----+
| id |
+----+
| 11 |
| 22 |
| 33 |
| 44 |
| 55 |
| 66 |
| 77 |
+----+
7 rows in set (0,00 sec)

mysql> select * from table_c;
+--------+
| id     |
+--------+
|    111 |
|    222 |
|    333 |
|    444 |
|    555 |
|    666 |
|    777 |
|    888 |
|    999 |
| 101010 |
| 111111 |
+--------+
11 rows in set (0,00 sec)

示例查询

mysql> SELECT * FROM (
    ->     SELECT * FROM (
    ->         SELECT ab.* FROM (
    ->             SELECT * FROM ( SELECT 1 as level,a.* FROM table_a AS a ORDER BY RAND() LIMIT 2) atmp
    ->             UNION
    ->             SELECT * FROM ( SELECT 2 as level ,b.* FROM table_b AS b ORDER BY RAND() LIMIT 6 ) btmp
    ->         ) AS ab ORDER BY ab.level LIMIT 6
    ->     ) AS totab
    -> UNION ALL
    ->     SELECT * FROM ( SELECT 3 as level,c.* FROM table_c AS c ORDER BY RAND() LIMIT 10) ctmp
    -> ) AS totabc ORDER BY totabc.level LIMIT 10;
+-------+--------+
| level | id     |
+-------+--------+
|     1 |      1 |
|     1 |      4 |
|     2 |     11 |
|     2 |     33 |
|     2 |     66 |
|     2 |     55 |
|     3 |    333 |
|     3 | 111111 |
|     3 |    777 |
|     3 |    888 |
+-------+--------+
10 rows in set (0,00 sec)

mysql>