mysql - COUNT(DISTINCT) 子句中的 WHERE 子句

Question

这是 Ryan Frank 在 forums.mysql.com 上提出的一个问题，我也面临着这个问题。

我的 SELECT 语句开头有以下内容:

SELECT accounts.id, accounts.company, accounts.first, accounts.last,
COUNT(DISTINCT accounts_log.login_time) AS visits,
COUNT(DISTINCT accounts_log.ip_address) AS visitors,
COUNT(DISTINCT documents_log.access_time) AS docs,
MAX(accounts_log.login_time) AS login_time
FROM accounts

这会返回我需要的所有变量；但是，我想将使用 COUNT(DISTINCT) 的变量限制在一个日期范围内。我不能在 FROM 子句之后使用 WHERE 子句。例如:

FROM accounts
WHERE accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to'

不起作用，因为它无法提供我需要的所有帐户。

我正在寻找类似的东西:

COUNT(DISTINCT accounts_log.login_time WHERE accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to') AS visits

附:我知道上面的方法不起作用并且语法选项已经用完了。

Answer 1

SELECT accounts.id, accounts.company, accounts.first, accounts.last,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then accounts_log.login_time else null end) AS visits,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then accounts_log.ip_address else null end) AS visitors,
COUNT(DISTINCT case when accounts_log.login_time >='$search_from' AND accounts_log.login_time <='$search_to' then documents_log.access_time else null end) AS docs,
MAX(accounts_log.login_time) AS login_time
FROM accounts