如何将外层SELECT的结果传入SQL子查询 实现单语句统计产品日调用量

错误原因分析 #

你之前的查询得到错误结果，是因为子查询没有和外层分组的invocation字段做关联，导致子查询统计的是全表所有日期下的A、B产品总调用量，不是对应日期的统计值。

方案1：条件聚合（推荐，性能最优） #

不需要嵌套子查询，直接通过聚合函数+条件判断即可实现需求，查询效率更高：

SELECT
  invocation,
  COUNT(*) AS total,
  SUM(CASE WHEN product_name = 'A' THEN 1 ELSE 0 END) AS count_a,
  SUM(CASE WHEN product_name = 'B' THEN 1 ELSE 0 END) AS count_b
FROM products
GROUP BY invocation
ORDER BY invocation DESC;

如果是支持IF函数的数据库（比如MySQL），还可以简化写法：

SELECT
  invocation,
  COUNT(*) AS total,
  SUM(IF(product_name = 'A', 1, 0)) AS count_a,
  SUM(IF(product_name = 'B', 1, 0)) AS count_b
FROM products
GROUP BY invocation
ORDER BY invocation DESC;

方案2：关联子查询（符合外层传参到内层的逻辑） #

如果需要保留外层查询传日期到内层子查询的写法，只需要在内层子查询中关联外层的日期字段即可：

SELECT
  outer_p.invocation,
  COUNT(*) AS total,
  (SELECT COUNT(*) FROM products p WHERE p.invocation = outer_p.invocation AND p.product_name = 'A') AS count_a,
  (SELECT COUNT(*) FROM products p WHERE p.invocation = outer_p.invocation AND p.product_name = 'B') AS count_b
FROM products outer_p
GROUP BY outer_p.invocation
ORDER BY outer_p.invocation DESC;

两种方案执行后都可以得到你期望的结果：

invocation  total  count_a  count_b
2021-09-08  3      2        1
2021-09-07  1      0        1

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