mysql - 使用MySQL聚合函数进行比较

Question

好的，在 MySQL 数据库中我有这个表:

id | value
 1 | 141
 1 | 142
 1 | 143
 2 | 144
 2 | 145
 2 | 148

我想按 id 对表格进行分组，并显示 value 低于最小 value 加 3 的行数.

上表的预期输出为:

id | counted
 1 | 3
 2 | 2

我尝试了这些查询，但没有一个有效:

SELECT id, COUNT(*) counted FROM table WHERE value < MIN(value)+3 GROUP BY id

SELECT id, COUNT(*) counted FROM table GROUP BY id HAVING value < MIN(value)+3

SELECT id, SUM(CASE WHEN value < MIN(value)+3 THEN 1 ELSE 0 END) counted FROM table GROUP BY id

有什么想法吗？

Answer 1

SELECT table.id id, 
       COUNT(*) counted
FROM   table 
       LEFT JOIN (SELECT id, 
                         MIN(value) m 
                  FROM   table 
                  GROUP  BY id) tmp 
              ON table.id = tmp.id 
WHERE  table.value < tmp.m + 3 
GROUP  BY table.id;

这个想法是左连接每个 id 的最小值，然后比较两行(值与最小值)。连接产生的临时表如下所示:

  table.   |    tmp.
id | value | id  |  m 
-----------------------
 1 |  141  |  1  |  141
 1 |  142  |  1  |  141
 1 |  143  |  1  |  141
 2 |  144  |  2  |  144
 2 |  145  |  2  |  144
 2 |  148  |  2  |  144

上述查询的输出是:

id | counted
 1 | 3
 2 | 2