在下面的查询中,它返回 12 条记录,而 from 子句中的查询(作为 t)返回 18 条记录,任何人都可以帮助此查询中的问题是什么?
SELECT count(abc.id) as total_this_month,t.*
FROM email_details abc
JOIN
(SELECT count(email_details.id) as total_emails,MAX(`email_details`.email_date) as email_date1, `email_details`.* FROM (`cld_users` join email_details on email_details.fk_user_id = cld_users.id) GROUP BY `email_details`.`email_title` ORDER BY `email_details`.`email_date` DESC) as t
ON abc.email_title = t.email_title
where (MONTH(abc.email_date)=MONTH(NOW()) AND YEAR(abc.email_date)=YEAR(NOW()))
group by t.email_title
ORDER BY t.`email_date` DESC
在查询中,您指定
where (MONTH(abc.email_date)=MONTH(NOW()) AND YEAR(abc.email_date)=YEAR(NOW()))
但是在子查询(返回 18 个结果的子查询)中,您有 6 封电子邮件,其月份不是 2014 年 12 月。这些电子邮件不可能通过显式排除它们的查询返回。
您也想要这些电子邮件,因此您可以获得 18 个结果?删除排除它们的WHERE
子句:
SELECT Count(abc.id) AS total_this_month,
t.*
FROM email_details abc
JOIN (SELECT Count(email_details.id) AS total_emails,
Max(`email_details`.email_date) AS email_date1,
`email_details`.*
FROM (`cld_users`
JOIN email_details
ON email_details.fk_user_id = cld_users.id)
GROUP BY `email_details`.`email_title`
ORDER BY `email_details`.`email_date` DESC) AS t
ON abc.email_title = t.email_title
GROUP BY t.email_title
ORDER BY t.`email_date` DESC
从那里开始,如果您想计算当月的电子邮件,只需替换:
SELECT Count(abc.id) AS total_this_month,
跟
SELECT SUM(CASE WHEN MONTH(abc.email_date)=MONTH(NOW()) AND YEAR(abc.email_date)=YEAR(NOW()) THEN 1 ELSE 0 END) AS total_this_month,