WHERE子句COUNT(DISTINCT)子句内部

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

Answer 2

您可以将条件放在LEFT JOIN的ON子句中：

 SELECT a.id, a.company, a.first, a.last,
 COUNT(DISTINCT al.login_time) AS visits
 FROM accounts a
 LEFT JOIN accounts_log al ON (al.account_id = a.id AND 
 al.login_time BETWEEN '$search_from' AND '$search_to')

其他表也是如此。