SQL查询：所有员工状态符合指定范围的公司及最后离职日期

First, let's break down why your original query didn't work. The id=all(...) condition tries to match a single employee's ID to every ID in the subquery, which isn't the right logic for checking if all employees in a company have valid opt_state values. Instead, we need to verify there are no employees in the company with an invalid opt_state, then retrieve the latest opt_out_on date for those companies.

Solution 1: Using NOT EXISTS #

This approach checks for companies where no employee has an opt_state outside your allowed list, then groups by company to get the latest opt-out date:

SELECT 
    company_id,
    MAX(opt_out_on) AS last_opt_out_date
FROM employees e
WHERE NOT EXISTS (
    -- Subquery checks if any employee in the company has an invalid opt_state
    SELECT 1
    FROM employees e2
    WHERE e2.company_id = e.company_id
      AND e2.opt_state NOT IN ('ceased_membership', 'ignition', 'opted_out')
)
GROUP BY company_id;

How it works:

• The NOT EXISTS clause ensures we only include companies where no employee has an opt_state that's not in your allowed set.
• MAX(opt_out_on) gives the most recent opt-out date for each qualifying company.
• GROUP BY company_id aggregates results at the company level.

Solution 2: Using GROUP BY and HAVING #

This method groups employees by company, then filters groups where all employees have valid opt_state values:

SELECT 
    company_id,
    MAX(opt_out_on) AS last_opt_out_date
FROM employees
GROUP BY company_id
HAVING COUNT(CASE WHEN opt_state NOT IN ('ceased_membership', 'ignition', 'opted_out') THEN 1 END) = 0;

How it works:

• GROUP BY company_id groups all employees by their company.
• The CASE statement in the COUNT function counts how many employees in the company have an invalid opt_state.
• The HAVING clause filters out any companies where this count is greater than 0 (i.e., only keep companies with no invalid employees).
• MAX(opt_out_on) retrieves the latest opt-out date for each valid company.

Both queries will give you the desired result: a list of companies where every employee's opt_state is in your allowed set, along with the last opt-out date for each company.

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