我有一个安装的简单表格:
- 产品代码
- 电子邮件
- install_slot
如果install_slot为NULL,则它是可用的安装插槽。不为空--那么,使用了slot。我需要返回给定产品和电子邮件的总安装结果,以及给定产品和邮件的已用安装结果。我想我可以用两个查询来完成这项工作,但想知道是否有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` = 'example@example.com'
GROUP BY
i1.`prod_code`,i2.`prod_code`
SELECT prod_code,
COUNT(email) AS total_installs,
COUNT(install_slot) AS used_installs
FROM installs
WHERE email='example@example.com'
GROUP BY prod_code
COUNT
只计算NOT NULL
的值。
提供的解决方案对我不起作用。我不得不修改如下:
SELECT prod_code,
COUNT(NULLIF(email,'')) AS total_installs,
COUNT(NULLIF(install_slot,'')) AS used_installs
FROM installs
WHERE email='example@example.com'
GROUP BY prod_code