如何在MySQL中用子查询表达计数（distinct）？ [英] How to express count(distinct) with subquery in MySQL?

问题描述

查询会产生一定数量的结果。查询是：

A query results a certain number. The query is:

select
count(distinct case when (A or B or C) and D then table_a.field1 else null end)
from table_a
    left join table_b on table_b.x = table_a.y
group by table_a.y
;

其中A，B，C和D是给定条件。现在，写成这种形式：

where A, B, C and D are given conditions. Now, written in this form:

select
sum((select count(1) from table_b where table_b.x = table_a.y and ((A or B or C) and D) ))
from table_a
    left join table_b on table_b.x = table_a.y
group by table_a.y
;

结果与count（distinct）不匹配。

the result does not match the one we got with count(distinct).

使用子查询写入count（distinct）的正确方式是什么？

What is the correct way of writing count(distinct) with a subquery?

推荐答案

不知道为什么你需要一个子查询。您仍然有JOIN，因此子查询可能会多次计数相同的行。

It's not at all clear why you need a subquery. You still have the JOIN, so that subquery is potentially going to be "counting" the same rows multiple times.

如果要获取在 table_a 中满足一组条件（在table_a上），那么你实际上不需要table_b上的子查询得到。至少，我还没有看到你可以使用table_b上的子查询获得结果。

If you want to get the number of distinct values for field1 in table_a which meets a set of criteria (on table_a), then you don't really need a subquery on table_b to get that. At least, I don't see anyway that you can get that result using a subquery on table_b.

这里是一个返回等效结果的示例：

Here's an example that returns an equivalent result:

 select (select sum(1) as mycount
           from ( select a.field1 
                    from table_a a
                    left join table_b on table_b.x = a.y
                   where a.y = t.y
                     and ( (A or B or C) and D )
                     and a.field1 IS NOT NULL
                   group by a.field1
                ) s
        ) as mycount
   from table_a t
  group by t.y

这真的是我知道得到等价于 COUNT（DISTINCT expr）的唯一方法。你必须做一个 SELECT expr FROM ... WHERE expr IS NOT NULL GROUP BY expr ，然后计算它返回的行。在这种情况下，您可以使用COUNT（1）或SUM（1）。

That's really the only way I know to get something equivalent to a COUNT(DISTINCT expr). You've got to do a SELECT expr FROM ... WHERE expr IS NOT NULL GROUP BY expr, and then count the rows it returns. In this case, you could use either a COUNT(1) or a SUM(1).

（我不能确定答案）

（我们注意到在你的原始查询中，你有一个 GROUP BY table_a.y

(We note that in your original query, you have a GROUP BY table_a.y, so that query can return multiple rows, each with its own count.

这篇关于如何在MySQL中用子查询表达计数（distinct）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！