如何调整多个'where'子句中的单个查询 - How can I adjust multiple 'where' clause in single query?

我有这个查询，它从结果中给我以前的id，但它包含多个where子句。

SELECT DISTINCT Contacts.cm_id 
    FROM Contacts 
    LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) AND (Employee.ei_primary_employer = "Y") 
WHERE ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
    AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
WHERE Contacts.cm_id < (
    SELECT DISTINCT Contacts.cm_id 
        FROM Contacts 
        LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) AND (Employee.ei_primary_employer = "Y") 
        WHERE ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
        WHERE Contacts.cm_id='77')
ORDER BY Contacts.cm_id DESC LIMIT 1

它给了我这个error

'WHERE Contacts.cm_id='313')) ORDER BY Contact_Master.cm_id DESC LIMIT 1' at line 10

如何调整此查询以避免查询中断?请帮助

您的查询语法列表错误，LEFT JOIN ... ON ...之后的AND应该在WHERE caluse中，并且每次将WHERE caluse与AND结合而不是WHERE，通常每个SELECT只有一个WHERE。

试试这样写:

SELECT DISTINCT Contacts.cm_id 
FROM Contacts LEFT JOIN Employee 
     ON (Contacts.cm_id = Employee.ei_contact_id) 
WHERE (Employee.ei_primary_employer = "Y") 
  AND ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
  AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
  AND Contacts.cm_id < 
     (SELECT DISTINCT Contacts.cm_id 
      FROM Contacts LEFT JOIN Employee 
           ON (Contacts.cm_id = Employee.ei_contact_id)  
      WHERE (Employee.ei_primary_employer = "Y") 
        AND (CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
        AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
        AND Contacts.cm_id='77') ORDER BY Contacts.cm_id DESC LIMIT 1)

格式化查询，使其可读如果在where子句中使用聚合函数，则使用

在

我认为你不应该有两个'WHERE'语句。将每个语句中的第二个'WHERE'改为'AND'

不能写多个where子句，除非它是另一个查询中的子查询。您可以根据需要通过放置"AND"或"or"来代替其他where来调整您的查询。

尝试下面的查询(我放置AND而不是WHERE，您可以根据您的需要调整AND)

SELECT DISTINCT Contacts.cm_id FROM Contacts 
LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) AND 
(Employee.ei_primary_employer = "Y") 
WHERE ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' AND 
CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
AND Contacts.cm_id < (SELECT DISTINCT Contacts.cm_id FROM Contacts LEFT JOIN Employee  
ON (Contacts.cm_id = Employee.ei_contact_id) AND (Employee.ei_primary_employer = "Y") 
AND ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' AND  
CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
AND Contacts.cm_id='77') 
ORDER BY Contacts.cm_id DESC LIMIT 1

*注意:*此查询未经过测试，仅供您参考。它可能会/可能不会成功。

谢谢

我建议您再次检查MySQL中的SELECT语法，因为您的查询完全错误。

由于我不知道数据库结构和你想要检索的原始任务，我不确定结果是否正确。

但是在你的查询中摆脱过度，我得到了这个:

SELECT MAX Contacts.cm_id as cm_id FROM Contacts LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) WHERE Employee.ei_primary_employer = "Y" AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' AND Contacts.cm_id < 77

祝你一切顺利