MySQL - 按总 LIMIT 选择 UNION 中每个的等量

Question

在 MySQL 数据库中，我有 3 个表:customers、projects 和 tasks。对于我正在实现的搜索，我想搜索所有 3 个表并选择找到的匹配项。问题是我想同样限制 MySQL 返回的结果数量。

这是我当前的查询示例:

SELECT id, title, type
FROM (
    (
    SELECT id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
LIMIT 6;

在此示例中，我想将结果限制为 6。

此示例我想要的最终结果如下:

1) 如果所有表格至少有 2 个结果，则每个表格显示 2 个结果。

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
48     'A project for testing'    'project'
17     'Test Project'             'project'
1      'Task test'                'task'
2      'Second test'              'task'

2) 如果一个表没有任何结果，则显示其他 2 个表中每一个的 3 个结果。 (如果只有一个表有结果，则显示该表的 6 个结果。)

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
56     'Customer test 56'         'customer'
1      'Task test'                'task'
2      'Second test'              'task'
3      'Test task'                'task'

3) 如果其中 2 个表有超过 2 个结果，而第三个表只有 1 个结果，则其中一个具有足够结果的表显示 3 个结果，两个表中的另一个表显示 2 个结果，第一个表显示 1 个结果表只有 1 个结果。

id     title                      type
20     'First test customer'      'customer'
48     'A project for testing'    'project'
17     'Test Project'             'project'
34     'Testing project'          'project'
1      'Task test'                'task'
2      'Second test'              'task'

有人可以帮我解决这个问题吗？

提前致谢!

Answer 1

您可以为每个 SELECT 使用行号，然后使用该计算字段对 UNION 进行排序，以平衡从单个查询获得的结果(我没有测试了此代码，请以此为起点):

SET @rank1=0;
SET @rank2=0;
SET @rank3=0;
SELECT id, title, type, rank
FROM (
    (

    SELECT @rank1:=@rank1+1 AS rank, id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank2:=@rank2+1 AS rank, id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank3:=@rank3+1 AS rank, id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
ORDER BY rank 
LIMIT 6;