我一直在为一个看起来足够简单的查询而挣扎。我有三张桌子，账户，联系人&活动。每次记录活动时，都会记录AccountID、Contact Subject和TimeDate。
我查询Activities表以显示每个AccountID上一周的所有活动计数。
我使用：
select
accounts.account as Account,
count(distinct activities.contactid) as Users,
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;
结果是这样的：
Account Users
ACME Ltd 4
Warner Bros 6
RBS 9
等等。。
activities表包含2000万行，大约在20秒内运行。
不过，我想要一份全面的清单。我想将结果与当月没有任何活动的AccountID列表结合起来。
Account Users
ACME Ltd 4
Warner Bros 6
RBS 9
Microsoft 0 or NULL
等等。。。
我试过这样联合：
select Account, '' from Accounts
UNION
select
accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts, activities
where activities.accountid=accounts.accountid
AND completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;
根据我对UNION的理解，它应该返回一个唯一的列表(没有重复(。但当我只有大约900个账户时，我得到的是大约1400个账户的列表。
我尝试过LEFT OUTER JOIN，但这似乎永远都会运行(我在2小时后杀死了它(
有人对我能尝试什么有什么建议吗？
感谢
这是您尝试的left outer join吗？
select accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts left outer join
activities
on activities.accountid=accounts.accountid
where completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and
completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;
这应该具有与原始查询大致相同的性能。
顺便说一下，您应该始终使用显式join语法(from子句中的join关键字(，而不是where子句中的隐式联接。
completeddate似乎在活动表中(总是使用别名(。在这种情况下，需要将其移动到on子句中：
select accounts.account as Account,
count(distinct activities.contactid) as Users
from accounts left outer join
activities
on activities.accountid=accounts.accountid and
activities.completeddate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY and
activities.completeddate < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
group by accounts.account asc;
这取决于您是否希望将select与"or"或"and"逻辑组合。
对于"或"，请使用UNION DISTINCT对于"answers"，请在主键上使用INNER JOIN。