排除计数为零的 MySQL 结果行

发布于 2024-12-05 15:38:05 字数 391 浏览 0 评论 0原文

我有以下 SQL 查询：

SELECT 
    COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country

这显示了按国家/地区分组的新用户数和总用户数。我想排除新用户计数为零的行，但我不确定如何在上面的 SQL 中执行此操作。目前我在 PHP 中跳过它们，但认为可能有更好的方法来编写查询。

欢迎任何建议。

原文

I have the following SQL query:

SELECT 
    COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country

This shows a count of new users and total users grouped by country. I'd like to exclude rows where the count of new users is zero, but I'm not sure how to do this in the above SQL. At the moment I'm skipping them in PHP but thought there might be a better way to write the query.

Any suggestions are welcome.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

童话里做英雄 2024-12-12 15:38:05

使用这个：
（注意 HAVING 子句）

SELECT 
    COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country 
HAVING COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) > 0

Use this:
(note the HAVING clause)

SELECT 
    COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country 
HAVING COUNT(CASE WHEN u.account_new = 1 THEN u.user_id END) > 0

回复收藏 0 原文

小鸟爱天空丶 2024-12-12 15:38:05

这包括更好地计算新用户的阅读改进：

SELECT 
    SUM(u.account_new=1) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country 
HAVING SUM(u.account_new=1)>0

This includes a better reading improvement for counting new users:

SELECT 
    SUM(u.account_new=1) AS new_user, 
    COUNT(u.user_id) AS all_users, 
    c.country_name AS country 
FROM users u, countries c 
WHERE u.country_id = c.country_id 
GROUP BY u.country 
HAVING SUM(u.account_new=1)>0

回复收藏 0 原文

~没有更多了~