mysql - 向我的 mysql 查询添加条件会导致无效使用组函数错误

Question

我试图通过组合 2 个 SQL 查询来获取结果集

1. 我必须通过以下查询获取特定应用的 max(user_count)

从 pg_datascience.hostapp_apps 组中选择 max(user_count) 作为 TotalUsers，根据 host_app_id 按 TotalUsers desc 顺序选择 host_app_id 作为 host_id；

我必须在此查询中从上面的查询中获取 TotalUsers 和 host_id，如下所示

从pg_datascience.hostapp_apps中选择distinct(app_id), max(user_count), round((max(user_count)/TotalUsers * 100),2) AS百分比，其中host_app_id = host_id group by app_id order by Percentage desc;

我尝试将这些查询组合到以下查询中的同一查询中:

select distinct(a.app_id), 
    max(a.user_count) as users, 
    round((max(a.user_count)/b.TotalUsers * 100 ),2) AS percentage, 
    b.TotalUsers 
from pg_datascience.hostapp_apps as a 
JOIN 
(
    select max(user_count) as TotalUsers, 
            host_app_id as host_id 
    from pg_datascience.hostapp_apps 
    group by host_app_id order by TotalUsers desc
) b 
on (host_app_id = b.host_id) 
group by a.app_id order by percentage desc;

现在我担心的是，如果ma​​x(a.user_count) as users小于10000，我必须过滤并删除记录

我尝试使用where子句

select distinct(a.app_id), 
    max(a.user_count) as users, 
    round((max(a.user_count)/b.TotalUsers * 100 ),2) AS percentage, 
    b.TotalUsers 
from pg_datascience.hostapp_apps as a 
JOIN 
(
    select max(user_count) as TotalUsers, 
    host_app_id as host_id 
    from pg_datascience.hostapp_apps 
    group by host_app_id 
    order by TotalUsers desc
) b 
on (host_app_id = b.host_id) **where 10000<=(max(a.user_count)** group by a.app_id order by percentage desc;

但是我收到了“无效使用组函数”错误。

然后我尝试为其编写一个单独的子查询

select distinct(a.app_id), 
        max(a.user_count) as users, 
        round((max(a.user_count)/b.TotalUsers * 100 ),2) AS percentage, 
        b.TotalUsers 
from pg_datascience.hostapp_apps as a 
JOIN 
(
    select max(user_count) as TotalUsers, 
            host_app_id as host_id 
    from pg_datascience.hostapp_apps 
    group by host_app_id 
    order by TotalUsers desc
) b 
on (host_app_id = b.host_id) 
where **10000<=(select max(a.user_count) from pg_datascience.hostapp_apps as a)** 
group by a.app_id 
order by percentage desc;

但我没有得到所需的结果，因为结果仍然是用户数>10000 的应用程序。

有没有其他方法可以得到想要的结果

Answer 1

认为您只需要一个 HAVING 子句来检查 GROUP BY 之后的值

SELECT a.app_id, 
    MAX(a.user_count) AS users, 
    ROUND((MAX(a.user_count)/b.TotalUsers * 100 ),2) AS percentage, 
    b.TotalUsers 
FROM pg_datascience.hostapp_apps AS a 
JOIN 
(
    SELECT host_app_id AS host_id,
            MAX(user_count) AS TotalUsers
    FROM pg_datascience.hostapp_apps 
    GROUP BY host_app_id 
) b 
on (host_app_id = b.host_id) 
GROUP BY a.app_id,
        b.TotalUsers
HAVING users >= 10000
ORDER BY percentage desc;