如何在Knex.js中对UNION ALL查询的第一个表达式使用GROUP BY？

问题场景 #

现有entries表与categories表通过category_id关联，entries表包含BOOLEAN类型的is_marked字段。需求是统计两类数据并合并结果：

• 每个分类下的条目数量
• 所有标记为is_marked = 1的条目总数

计划用UNION ALL实现，预期生成的SQL如下：

(
SELECT 
    cat.category_name AS `name`, 
    cat.category_id AS `id`, 
    COUNT(DISTINCT e.entry_id) AS `entry_count` 
FROM `categories` AS `cat` 
    INNER JOIN `entries` AS `e` USING (category_id)
GROUP BY cat.category_name
) UNION ALL (
SELECT
    "Marked Entry" AS `name`, 
    -1 AS `id`, 
    COUNT(DISTINCT e2.entry_id) AS `entry_count` 
 FROM `entries` AS `e2` 
    WHERE `e2`.`is_marked` = 1
)

但原Knex代码生成的SQL错误地将GROUP BY放到了UNION ALL之后，触发MySQL 1140错误，生成的错误SQL如下：

select 
    `cat`.`category_name` as `name`, 
    `cat`.`category_id` as `id`, 
    count(distinct `e`.`entry_id`) as `entry_count` 
from `categories` as `cat` 
    inner join `entries` as `e`
    on `e`.`category_id` = `cat`.`category_id`
union all
select
    "Marked Entry" AS `name`, 
    -1 AS `id`, 
    count(distinct `e2`.`entry_id`) as `entry_count` 
from `entries` as `e2`
    where `e2`.`is_marked` = ?
group by `cat`.`category_name`

解决方案 #

使用Knex的wrap()方法，将每个子查询用括号包裹，强制GROUP BY仅作用于第一个子查询。修改后的Knex代码如下：

const categoryQuery = knex
    .select('cat.category_name AS name', 'cat.category_id AS id')
    .countDistinct('e.entry_id AS entry_count')
    .from('categories AS cat')
    .innerJoin('entries AS e', 'e.category_id', 'cat.category_id')
    .groupBy('cat.category_name')
    .wrap('(', ')'); // 给第一个子查询添加括号

const markedQuery = knex
    .select(knex.raw('"Marked Entry" AS `name`'), knex.raw('-1 AS `id`'))
    .countDistinct('e2.entry_id AS entry_count')
    .from('entries AS e2')
    .where('e2.is_marked', '=', 1)
    .wrap('(', ')'); // 给第二个子查询添加括号

const finalQuery = categoryQuery.unionAll([markedQuery]);

原理说明 #

Knex默认生成的UNION ALL不会自动给子查询添加括号，导致GROUP BY被提升到整个联合查询的外层。通过wrap('(', ')')方法，让每个子查询都被独立包裹，确保GROUP BY只属于第一个子查询，生成的SQL会与预期一致。

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