合并SQL带WHERE条件的查询与JOIN关联查询报错如何解决

错误原因 #

你合并SQL时删除了原关联查询里的GROUP BY t1.id语句，GROUP_CONCAT属于聚合函数，使用聚合函数时如果没有加GROUP BY，SQL会默认把整个结果集当成一组聚合，此时SELECT子句中直接查询非聚合的普通字段，就会触发sql_mode=only_full_group_by的合规校验报错。

最优解决写法 #

建议先完成商品过滤再做关联聚合，提前过滤无效数据可以大幅提升查询性能：

SELECT t1.*, GROUP_CONCAT('[','name:',t2.fileName,',','id:', t2.id,']') AS gallery
FROM (
    -- 先筛选符合条件的目标商品
    SELECT * FROM products
    WHERE isLive IS FALSE 
      AND timeClose IS NULL 
      AND (timeOpen IS NULL OR timeOpen <= 1)
    ORDER BY timeOpen ASC
    LIMIT 11
) AS t1
JOIN gallery AS t2 ON t1.id = t2.productId
GROUP BY t1.id;

如果需要保留没有对应相册记录的商品，把JOIN改为LEFT JOIN即可，此时无相册的商品gallery字段值为NULL。

原写法修复方案（性能较差，不推荐） #

如果要保留你原有的查询逻辑，只需要在子查询的聚合语句后补上丢失的GROUP BY t1.id即可：

SELECT * FROM (
    SELECT t1.* , GROUP_CONCAT('[','name:',t2.fileName,',','id:', t2.id,']') AS gallery
    FROM products AS t1
    JOIN gallery AS t2 ON t1.id=t2.productId
    GROUP BY t1.id
) as t1
WHERE t1.isLive IS FALSE AND
t1.timeClose IS NULL AND
( t1.timeOpen is NULL OR t1.timeOpen <= 1 )
ORDER BY t1.timeOpen ASC
LIMIT 11

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