mysql - 在MySQL中进行子查询时如何使用“分组依据”？

我有以下查询：

SELECT c.text1, sum(c.num1), sum(c.num2), sum(c.num3),
    (SELECT count(id) FROM table2 WHERE type = 1 AND txt = c.text1 AND spec_id = c.sp_id)
FROM table1 as c
WHERE c.type = 1
GROUP BY c.text1, c.sp_id

是否有一种解决方法以某种方式从GroupBy子句中删除c.sp_id？我知道，如果删除它，MySQL将返回错误。

还是有办法仅按c.text1将查询结果分组？

最佳答案

如果我正确理解问题，则需要进行两个单独的汇总。这是查询的一种版本：

SELECT c.text1, c.sum1, c.sum2, c.sum3, t2.cnt
FROM (SELECT c.text1, sum(c.num1) as sum1, sum(c.num2) as cum2, sum(c.num3) as sum3
      FROM table1 c
      GROUP BY c.text1
     ) c LEFT JOIN
     (SELECT txt, count(*) as cnt
      FROM table2 t2
      WHERE t2.type = 1 AND
            EXISTS (SELECT 1
                    FROM table1 c2
                    WHERE t.txt = c2.txt AND c2.type = 1 AND
                          t.spec_id = c2.sp_id
                   )
     ) t2
     ON t2.txt = c.text1
WHERE c.type = 1;

关于mysql - 在MySQL中进行子查询时如何使用“分组依据”？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48521582/