mysql - 如何使聚合查询返回空集而不是 NULL 行？

Question

我有一个这样的 SQL 查询:

SELECT t1.name, MAX(t2.value)
FROM t2
JOIN t1 ON t1.id = t2.t1_id
WHERE t2.t1_id = 1 AND t2.text_id = 16;

但是，当 t2 选择为空时，它返回包含 NULL 值的行(因为在空集上调用 MAX 函数时返回 NULL)。我希望它返回一个空集。我怎样才能实现它？

Answer 1

Having 子句非常适合这里:

SELECT
  t1.name, MAX(t2.value)
FROM
  t2 JOIN t1 ON t1.id = t2.t1_id
WHERE
  t2.t1_id = 1 AND t2.text_id = 16
-- GROUP BY something?
HAVING
  MAX(t2.value) IS NOT NULL