mysql - 如果找到的值少于 2 个，则忽略选择

Question

我连接了 3 个表来获取我想要的数据:company_details、officer_details 和 company_officer 将这两个表连接起来。我现在的查询对象是在 2 家以上公司工作且尚未辞职的高管。

我想添加到此查询中，以仅获取拥有超过 1 名员工的公司(在应用所有先前的要求之后)。

公司详细信息

公司_官员

官员详细信息

SELECT 
                            CD.Company_ID,
                            CD.Company_Name,
                            CD.Company_Index,
                            OD.Officer_ID,
                            OD.Officer_Name,
                            CO.Officer_Role
                        FROM
                            Company_Details CD
                        INNER JOIN Company_Officer CO 
                           ON CD.Company_ID = CO.Company_ID
                        INNER JOIN Officer_Details OD 
                           ON CO.Officer_ID = OD.Officer_ID
                        WHERE CD.Company_Index<>'' AND 
                              CO.Resigned_On='' AND
                              CO.Officer_ID IN
                               ( SELECT CO2.officer_id
                                 FROM   Company_Officer CO2
                                 INNER JOIN Company_Details CD2
                                 ON CO2.Company_ID = CD2.Company_ID
                                 WHERE CO2.Resigned_On='' AND CD2.Company_Index<>''
                                 GROUP  BY CO2.officer_id
                                 HAVING Count( DISTINCT CO2.company_id ) > 1 
                               )
                        ORDER BY `CD`.`Company_Name` ASC, `CD`.`Company_ID` ASC;

在下图中，您可以看到我当前查询的结果。我想省略突出显示的 2 行，因为您可以看到这些公司只有一名员工，而我想从只有一名员工的选择公司中删除。

Answer 1

只需向Where 子句添加另一个谓词

SELECT  CD.Company_ID, CD.Company_Name, CD.Company_Index,
    OD.Officer_ID, OD.Officer_Name, CO.Officer_Role
FROM Company_Details CD
    JOIN Company_Officer CO 
        ON CD.Company_ID = CO.Company_ID
    JOIN Officer_Details OD 
        ON CO.Officer_ID = OD.Officer_ID
WHERE CD.Company_Index<>'' 
     AND CO.Resigned_On='' 
     AND CO.Officer_ID IN
        ( SELECT CO2.officer_id
          FROM Company_Officer CO2
             JOIN Company_Details CD2
                  ON CO2.Company_ID = CD2.Company_ID
          WHERE CO2.Resigned_On='' AND CD2.Company_Index<>''
          GROUP  BY CO2.officer_id
          HAVING Count( DISTINCT CO2.company_id ) > 1)
And not Exists (Select * from company_officer
                Where Company_Id = cd.Company_Id 
                Having count(*) < 1)
ORDER BY `CD`.`Company_Name` ASC, `CD`.`Company_ID` ASC;

此外，您可以使用 Exists 而不是 In 简化第二个谓词

SELECT  CD.Company_ID, CD.Company_Name, CD.Company_Index,
    OD.Officer_ID, OD.Officer_Name, CO.Officer_Role
FROM Company_Details CD
    JOIN Company_Officer CO 
        ON CD.Company_ID = CO.Company_ID
    JOIN Officer_Details OD 
        ON CO.Officer_ID = OD.Officer_ID
WHERE Not Exists
        (Select * FROM Company_Officer
         WHERE officer_id = od.officer_id 
            and Resigned_on = ''
         Having count(*) <= 2)
     And Not Exists 
        (Select * from company_officer
         Where Company_Id = cd.Company_Id 
         Having count(*) <= 1)
ORDER BY `CD`.`Company_Name` ASC, `CD`.`Company_ID` ASC;