COUNT（*）返回GROUP BY子句之前的行数？

I have the follwing query:

我有以下查询:

SELECT COUNT(*)
FROM mydb.table1 t1
JOIN mydb.table2 t2 ON t1.id = t2.t1_id
WHERE t1.user_id = 44 AND t1.date_deleted IS NULL
GROUP BY t2.system_id, CASE WHEN t2.system_id IS NULL THEN t2.id ELSE 0 END

It returns COUNT(*) = 6, when it should be returning 1 since all six rows for this user have the same t2.system_id (so they should be grouped).

它返回COUNT(*)= 6,因为它应返回1,因为此用户的所有六行都具有相同的t2.system_id(因此它们应该被分组)。

If I change the query to select * instead of COUNT(*), it only returns a single row. If I then remove the GROUP BY clause, six rows are returned.

如果我将查询更改为选择*而不是COUNT(*),它只返回一行。如果我然后删除GROUP BY子句,则返回六行。

This makes me think COUNT(*) is returning the row count before the GROUP BY clause is executed, but from what I've read that's not how it's supposed to work.

这让我觉得COUNT(*)在执行GROUP BY子句之前返回行计数,但是从我读过的那些不是它应该如何工作。

Is this behavior normal?

这种行为是否正常?

2 个解决方案

#1

Try this:

select count(*) from (
  SELECT *
    FROM mydb.table1 t1
    JOIN mydb.table2 t2 ON t1.id = t2.t1_id
    WHERE t1.user_id = 44 
      AND t1.date_deleted IS NULL
    GROUP BY t2.system_id, 
      CASE WHEN t2.system_id IS NULL THEN t2.id ELSE 0 END
) q1

count gives you the number of (not null) items in each group, so yes, it is definitely working the way it is intended. This means that if you just want the total number of groups, the easiest way is to just wrap it in another query.

count给出了每个组中(非空)项的数量,所以是的,它肯定按照预期的方式工作。这意味着如果您只想要组的总数,最简单的方法是将其包装在另一个查询中。

#2

It returns the count of items in each group. You have one group with six items in it, so it returns one row containing a column valued 6.

它返回每个组中的项目数。您有一个包含六个项目的组,因此它返回一行,其中包含一个值为6的列。

#1