MySQL如何按total_number>1且parent_livestock_species_id为NULL排除记录？

当然可以实现这个需求！因为你的total_number是通过子查询计算出来的聚合字段，没法直接在原WHERE子句里用它做过滤，不过我们有两种简单的方式来搞定这个逻辑：

方法1：使用HAVING子句过滤聚合结果 #

HAVING子句专门用来过滤已经计算好的聚合结果，刚好适合你的场景。直接在原查询末尾加上过滤条件就行：

SELECT 
    LS.id AS livestock_id, 
    LS.parent_livestock_species_id AS parent_livestock_species_id, 
    LS.livestock_species_name_en AS livestock_species_name_en, 
    IFNULL(LSN.livestock_species_name, LS.livestock_species_name_en) AS livestock_species_name, 
    LSN.description AS description, 
    LS.image_link AS image_link, 
    (SELECT COUNT(*) FROM LivestockSpecies ls2 WHERE ls2.id = 1 OR ls2.parent_livestock_species_id = 1) AS total_number 
FROM LivestockSpecies AS LS 
LEFT JOIN LivestockSpeciesName AS LSN 
    ON LSN.livestock_species_id = LS.id AND LSN.language_id = 1 
WHERE LS.id = 1 OR LS.parent_livestock_species_id = 1
HAVING NOT (total_number > 1 AND parent_livestock_species_id IS NULL);

这里的NOT (total_number > 1 AND parent_livestock_species_id IS NULL)意思是：只要不是「总记录数大于1且父物种ID为空」的情况，就保留这条记录，刚好符合你要排除指定记录的需求。

方法2：用子查询先获取结果，再在外层过滤 #

如果觉得HAVING不太好理解，也可以把原查询包装成一个子查询，在外层用WHERE过滤掉不符合条件的记录，逻辑更直观：

SELECT *
FROM (
    SELECT 
        LS.id AS livestock_id, 
        LS.parent_livestock_species_id AS parent_livestock_species_id, 
        LS.livestock_species_name_en AS livestock_species_name_en, 
        IFNULL(LSN.livestock_species_name, LS.livestock_species_name_en) AS livestock_species_name, 
        LSN.description AS description, 
        LS.image_link AS image_link, 
        (SELECT COUNT(*) FROM LivestockSpecies ls2 WHERE ls2.id = 1 OR ls2.parent_livestock_species_id = 1) AS total_number 
    FROM LivestockSpecies AS LS 
    LEFT JOIN LivestockSpeciesName AS LSN 
        ON LSN.livestock_species_id = LS.id AND LSN.language_id = 1 
    WHERE LS.id = 1 OR LS.parent_livestock_species_id = 1
) AS subquery
WHERE NOT (total_number > 1 AND parent_livestock_species_id IS NULL);

注意事项 #

• 判断字段是否为NULL必须用IS NULL，不能用= NULL——这是SQL的语法规则，= NULL永远不会返回true。
• 两种方法的效果完全一致，你可以根据自己的习惯选择。如果你的数据集很大，可能需要测试一下哪种方法性能更好，不过大部分场景下差异不大。

内容的提问来源于stack exchange，提问作者AndreaNobili