MySQL - 如何计算空值和非空值？

发布于 2025-01-05 01:36:03 字数 741 浏览 0 评论 0原文

我有一个简单的安装表：

prod_code
email
install_slot

如果 install_slot 为 NULL，则它是可用的安装槽。不为空——那么，使用槽。我需要返回给定产品和电子邮件的总安装结果，以及给定产品和电子邮件的已使用安装结果。我想我可以用两个查询来完成此操作，但想知道是否有一种 SQL 方法可以在一个查询中完成这一切？

我尝试了以下大胆猜测，但它不起作用。

SELECT
    i1.`prod_code`,
    COUNT(i1.`email`) AS total_installs,
    COUNT(ISNULL(i2.`install_slot`)) AS used_installs
FROM
    `installs` AS i1
JOIN
    `installs` AS i2
ON
    i1.`prod_code` = i2.`prod_code`
WHERE
    i1.`email` = '[email protected]'
GROUP BY
    i1.`prod_code`,i2.`prod_code`

原文

I have a simple table of installs:

prod_code
email
install_slot

If the install_slot is NULL, then it's an available install slot. Not null -- then, used slot. I need to return a result of total installs for a given product and email, as well as a result of used installs for a given product and email. I guess I could do this with two queries, but wondered if there's a SQL way to do it all in one?

I tried the following as a wild guess, but it didn't work.

SELECT
    i1.`prod_code`,
    COUNT(i1.`email`) AS total_installs,
    COUNT(ISNULL(i2.`install_slot`)) AS used_installs
FROM
    `installs` AS i1
JOIN
    `installs` AS i2
ON
    i1.`prod_code` = i2.`prod_code`
WHERE
    i1.`email` = '[email protected]'
GROUP BY
    i1.`prod_code`,i2.`prod_code`

分享到QQ

分享到微博

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

发布评论

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

倾城花音 2025-01-12 01:36:03

SELECT prod_code,
       COUNT(email) AS total_installs,
       COUNT(install_slot) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

COUNT 仅计算 NOT NULL 值。

SELECT prod_code,
       COUNT(email) AS total_installs,
       COUNT(install_slot) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

COUNT counts NOT NULL values only.

回复收藏 0 原文

破晓 2025-01-12 01:36:03

提供的解决方案对我不起作用。我必须修改如下：

SELECT prod_code,
       COUNT(NULLIF(email,'')) AS total_installs,
       COUNT(NULLIF(install_slot,'')) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

The solution offered did not work for me. I had to modify as follows:

SELECT prod_code,
       COUNT(NULLIF(email,'')) AS total_installs,
       COUNT(NULLIF(install_slot,'')) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code

回复收藏 0 原文

~没有更多了~