MySQL - 我如何计算空值而不是空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9245038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - How Do I Count Nulls and Not Nulls?
提问by Volomike
I have a simple table of installs:
我有一个简单的安装表：
- prod_code
- install_slot
- prod_code
- 电子邮件
- 安装插槽
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?
如果 install_slot 为 NULL，则它是一个可用的安装槽。不为空——然后，使用插槽。我需要返回给定产品和电子邮件的总安装结果，以及给定产品和电子邮件的使用安装结果。我想我可以用两个查询来做到这一点，但想知道是否有一种 SQL 方法可以一次性完成所有这些工作？
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`
回答by dgw
SELECT prod_code,
COUNT(email) AS total_installs,
COUNT(install_slot) AS used_installs
FROM installs
WHERE email='[email protected]'
GROUP BY prod_code
COUNTcounts NOT NULLvalues only.
COUNTNOT NULL只计算值。
回答by Gary Lesperance
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
