MySQL未分组查询仅返回单条结果问题排查与解决

问题背景 #

我正在为产品筛选构建独立索引表，现有数十万产品及数百个属性术语。此前指定特定object_id和分类术语时，该索引表可返回70万+记录，但当前未使用GROUP BY子句的查询仅返回1条结果，无法获取所有产品的所有属性术语。

原查询语句及执行结果 #

SELECT term_relation.term_taxonomy_id, term_relation.object_id, term_tax.taxonomy, 
term_tax.term_id, terms.name, COUNT(term_tax.term_id) as count
FROM `wp_term_relationships` as term_relation
INNER JOIN wp_term_taxonomy AS term_tax USING(term_taxonomy_id)
INNER JOIN wp_terms AS terms USING(term_id)
WHERE term_relation.object_id IN (SELECT ID FROM wp_posts WHERE post_type IN 
('product','product_variation'))
AND term_tax.taxonomy IN (SELECT concat('pa_', attribute_name) AS taxonomy
FROM wp_woocommerce_attribute_taxonomies);

您的SQL查询已成功执行，但仅返回1条结果

添加GROUP BY后的结果 #

• 按term_id分组：

  GROUP BY term_id返回：显示0-24行（共11463条，查询耗时0.0001秒）

• 按object_id分组：

  GROUP BY object_id返回：显示0-24行（共92951条，查询耗时0.8672秒）

单独执行子查询的结果 #

基础关联查询（无WHERE条件） #

SELECT term_relation.term_taxonomy_id, term_relation.object_id, term_tax.taxonomy, 
term_tax.term_id, terms.name, COUNT(term_tax.term_id) as count
FROM `wp_term_relationships` as term_relation
INNER JOIN wp_term_taxonomy AS term_tax USING(term_taxonomy_id)
INNER JOIN wp_terms AS terms USING(term_id)

您的SQL查询已成功执行，但仅返回1条记录

产品ID查询 #

SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation')

显示0-24行（共110051条，查询耗时0.0006秒）

属性分类查询 #

SELECT concat('pa_', attribute_name) AS taxonomy FROM wp_woocommerce_attribute_taxonomies

显示0-24行（共174条，查询耗时0.0003秒）

问题原因 #

问题核心在于COUNT(term_tax.term_id) as count这个聚合函数：

• 当SQL使用聚合函数但未指定GROUP BY时，MySQL会把整个结果集当作一个单一分组，仅返回1条汇总记录（所有行的计数总和），其他非聚合列的值为随机选取（取决于MySQL的SQL模式，如ONLY_FULL_GROUP_BY是否开启）。
• 添加GROUP BY后，分组维度的不同会导致结果被聚合，无法返回所有产品与属性的关联明细。

解决方案 #

需求1：获取所有产品的所有属性术语明细 #

若不需要统计计数，直接移除聚合函数即可，查询会返回所有符合条件的关联记录：

SELECT term_relation.term_taxonomy_id, term_relation.object_id, term_tax.taxonomy, 
term_tax.term_id, terms.name
FROM `wp_term_relationships` as term_relation
INNER JOIN wp_term_taxonomy AS term_tax USING(term_taxonomy_id)
INNER JOIN wp_terms AS terms USING(term_id)
WHERE term_relation.object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'))
AND term_tax.taxonomy IN (SELECT concat('pa_', attribute_name) AS taxonomy FROM wp_woocommerce_attribute_taxonomies);

需求2：同时获取明细及统计信息 #

若需要在返回明细的同时附加统计数据（如每个产品关联的属性总数），可使用窗口函数（MySQL 8.0+支持）：

SELECT term_relation.term_taxonomy_id, term_relation.object_id, term_tax.taxonomy, 
term_tax.term_id, terms.name,
COUNT(term_tax.term_id) OVER (PARTITION BY term_relation.object_id) as product_attr_count
FROM `wp_term_relationships` as term_relation
INNER JOIN wp_term_taxonomy AS term_tax USING(term_taxonomy_id)
INNER JOIN wp_terms AS terms USING(term_id)
WHERE term_relation.object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'))
AND term_tax.taxonomy IN (SELECT concat('pa_', attribute_name) AS taxonomy FROM wp_woocommerce_attribute_taxonomies);

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