时间戳

首页 > 解决方案 > 根据 where 语句从子查询中选择多个结果计数？

sql - 根据 where 语句从子查询中选择多个结果计数？

问题描述

因此，我从子查询中进行选择，并根据创建的列中的值是否具有值来显示这些行的计数（使用 where 语句）。

我想创建一个查询，该查询根据不同的 where 语句返回多个计数。

我的表有两列：ID1 和 abc2。

select count(*) from
(
select `id1`, count(distinct(`abc2`)) as total_abc
from TABLE
where `id1` != 'Unknown' and `abc2` != 'NULL'
group by `id1`
order by total_abc desc
)
where total_abc = 1




select count(*) from
(
select `id1`, count(distinct(`abc2`)) as total_abc
from TABLE
where `id1` != 'Unknown' and `abc2` != 'NULL'
group by `id1`
order by total_abc desc
)
where total_abc = 2





select count(*) from
(
select `id1`, count(distinct(`abc2`)) as total_abc
from TABLE
where `id1` != 'Unknown' and `abc2` != 'NULL'
group by `id1`
order by total_abc desc
)
where total_abc = 3

所需的结果只需结合以下几个查询：

count1 | count2 | count3
__________________________
123    | 222     | 34567

标签： sql

解决方案

您可以使用两个级别的聚合：

select
    count(case when num_vals = 1 then 1 end) as val_1,
    count(case when num_vals = 2 then 1 end) as val_2,
    count(case when num_vals = 3 then 1 end) as val_3
from
(
    select id1, count(distinct abc2) as num_vals
    from TABLE
    where id1 <> 'Unknown' and abc2 <> 'NULL'
    group by id1
) t;

您似乎想要列中的值。我希望它们成行：

select num_vals, count(*)
from (select id1, count(distinct abc2) as num_vals
      from TABLE
      where id1 <> 'Unknown' and abc2 <> 'NULL'
      group by id1
     ) t
group by num_vals
order by num_vals;

推荐阅读