mysql - 从 JOIN 中加入 WHERE

Question

这个问题已经有答案了:

Can you use an alias in the WHERE clause in mysql? (5 个回答)

已关闭 3 年前。

我有多个表，其中包含一些客户端的数据。我想要实现的是输出某个客户在计费的第一个月的计费金额。

所以我运行类似于下面的代码:

SELECT company,clientid,COALESCE (signed.value,reactivated.value) as 'Activation' , Amount FROM `tblclients`
LEFT JOIN tblcustomfieldsvalues  as signed  ON tblclients.clientid = signed.relid and signed.fieldid = 5
LEFT JOIN tblcustomfieldsvalues  as reactivated ON tblclients.userid = reactivated.relid and reactivated.fieldid = 27
LEFT JOIN (
    SELECT clientid,sum(total) as Amount FROM tblinvoices  
    WHERE month(invoicedate)=month(Activation) Group by clientid) as f on tblclients.clientid = f.clientid 

我的问题是，当我执行最后一次连接时，它会出现错误:“where 子句”中的未知列“激活”。如果我将其切换为 current_date，则查询的其余部分将起作用。

关于如何实现这项工作有什么想法吗？

后来编辑:我可能过于简化了查询，我还有一个 COALESCE

Answer 1

您可以尝试以下 -

SELECT company,clientid,activated.value as Activation , Amount 
FROM `tblclients`LEFT JOIN tblcustomfieldsvalues as signed ON tblclients.clientid = signed.relid and signed.fieldid = 5
LEFT JOIN 
( SELECT clientid,sum(total) as Amount 
  FROM tblinvoices Group by clientid
) as f on tblclients.clientid = f.clientid and month(invoicedate)=month(Activation)