mysql - 需要一个按值的孪生组

Question

我有这样的查询，

SELECT SOME_ID, COUNT(ANOTHER_ID) AS WITHOUT_FILTER 
from SOME_TABLE GROUP BY SOME_ID

这让我回来了

+------------+------------------+
| SOME_ID    | WITHOUT_FILTER   |
+------------+------------------+
|  1         | 40               |
|  2         | 30               |
+------------+------------------+

我有相同的查询，条件为我提供过滤值。

SELECT SOME_ID, COUNT(ANOTHER_ID) AS WITH_FILTER 
from SOME_TABLE WHERE SOME_COL > 10 GROUP BY SOME_ID

在 grouped_by 部分返回的值明显较小

+------------+----------------+
| SOME_ID    | WITH_FILTER    |
+------------+----------------+
|  1         | 20             |
|  2         | 15             |
+------------+----------------+

现在，我需要一个查询来为我提供两个计数值，即在一个查询中有条件和无条件。结果应该是这样的

+----------+----------------+---------------+
| SOME_ID  | WITHOUT_FILTER | WITH_FILTER   |
+----------+----------------+---------------+
|  1       | 40             | 20            |
|  2       | 30             | 15            |
+------------+--------------+---------------+

请帮帮我。

Answer 1

你可以这样做:

SELECT 
    SOME_ID, 
    COUNT(ANOTHER_ID) AS WITHOUT_FILTER
    SUM(case when SOME_CONDITION then 1 else 0 end) AS WITH_FILTER

from SOME_TABLE GROUP BY SOME_ID