mysql where 子句跨越多年？

Question

我有一个 mysql 查询，它输出几年的销售线索与销售额，并计算转换率，该查询在 2012 年效果很好，因为它只保存了一年的数据。现在已经是 2013 年了，我需要改变它的工作方式，但陷入了困境。如果 2012 年 12 月出现潜在客户，然后在 2013 年成为销售，则它会从我的查询输出中省略，因为它只要求 2013 年的 CaseDate。

Select
  q.*,
  ROUND(100 * q.Comms / q.Total, 2) As Conversion,
  If(q.Adviser Is Null, 1, 0) As remove
From
  (Select
      a.ContactFullName As Adviser,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 1) As Jan,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 2) As Feb,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 3) As Mar,
      ..... Dec
      Sum(Case
        When Year(b.CaseDate) = 2013 And Month(b.CaseDate) Between '1'
        And '12' Then 1 Else 0 End) As Total,
      Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = ".(int)$_POST['year']." And Month(b.StatusSubmittedDate) Between ".(int)$_POST['start']." And ".(int)$_POST['end']." Then 1 Else 0 End) As Comms
    From
      tblcontacts a Inner Join
      tblcases b On a.ContactID = b.ContactAssignedTo
    Group By
      a.ContactFullName With Rollup) q

这有效；

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = 2013 And Month(b.CaseDate) Between '1' And '12'  Then 1 Else 0 End) As Comms

这仅显示 2013 年输入和提交的潜在客户；

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = ".(int)$_POST['year']." And Month(b.CaseDate) Between ".(int)$_POST['start']." And ".(int)$_POST['end']." Then 1 Else 0 End) As Comms

非常感谢

Answer 1

这取决于您的业务逻辑。如果逻辑表明您只显示同一年的潜在客户，那么您的代码按原样是完全正确的(按原样？)。如果您想包含 2012 或 2013(看起来像您这样做)，则需要完整拼写出来，服务器无法理解“2012 或 2013”​​

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(....

请注意 Or 语句周围的括号，这是为了确保将 CaseYear=2012 或 CaseYear=2013 和 SubmitYear=2013 视为 (CaseYear=2012 或 CaseYear=2013)和 SubmitYear=2013 而不是 CaseYear=2012 或(CaseYear=2013 和 SubmitYear=2013)

当然，如果业务逻辑不关心 CaseYear，则将其从 Sum 中取出。