SQL分组处理问询：将含Dept Type为A的部门所有记录统一设为A

There are a couple of clean, efficient ways to achieve this in SQL, depending on your preference for readability or conciseness. Here are two common approaches:

Approach 1: Using a Subquery and Left Join #

This method first identifies all departments that have at least one record with Dept Type = 'A', then joins that list back to the original table to conditionally set the Dept Type.

SELECT
    original.Dept,
    original.Sub_Dept,
    -- If the department has an 'A' record, use 'A'; else keep original value
    CASE WHEN depts_with_a.Dept IS NOT NULL THEN 'A' ELSE original.Dept_Type END AS Dept_Type
FROM
    your_table original
LEFT JOIN (
    -- Get all unique departments that have at least one 'A' entry
    SELECT DISTINCT Dept
    FROM your_table
    WHERE Dept_Type = 'A'
) depts_with_a ON original.Dept = depts_with_a.Dept;

Approach 2: Using Window Functions #

This approach uses a window function to count how many 'A' entries exist in each department, then uses that count to decide the Dept Type. It avoids the need for a separate join.

SELECT
    Dept,
    Sub_Dept,
    CASE
        -- If there's at least one 'A' in the department, set to 'A'
        WHEN COUNT(CASE WHEN Dept_Type = 'A' THEN 1 END) OVER (PARTITION BY Dept) > 0 THEN 'A'
        -- Otherwise, keep the original Dept Type
        ELSE Dept_Type
    END AS Dept_Type
FROM
    your_table;

Key Notes: #

• Replace your_table with the actual name of your table.
• Both approaches will produce the same result:
  • For the Sales department (which has an 'A' entry), all records will have Dept Type = 'A'.
  • For the Operations department (no 'A' entries), all records retain their original Dept Type values.

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