如何编写SQL CASE语句标识用户是否拥有服务3、4或两者兼具？

Got it, let's tackle this problem. The core challenge here is that we need to evaluate all services a user has across their entire set of rows, not just the single service in the current row. Window functions are perfect for this because they let us compute values across a group (in this case, each user's records) without collapsing the rows.

Here's the SQL query that will produce your desired output:

SELECT 
    ID,
    Name,
    Age,
    GroupID,
    Services,
    CASE
        WHEN has_3 = 1 AND has_4 = 1 THEN '3 & 4'
        WHEN has_3 = 1 THEN '3 Only'
        WHEN has_4 = 1 THEN '4 Only'
        ELSE 'No Matching Services' -- Optional, for handling other service IDs
    END AS SG
FROM (
    SELECT 
        *,
        -- Check if user has at least one record with Service 3
        MAX(CASE WHEN Services = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Name) AS has_3,
        -- Check if user has at least one record with Service 4
        MAX(CASE WHEN Services = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Name) AS has_4
    FROM your_table_name -- Replace with your actual table name
) AS user_service_flags;

How This Works: #

• Subquery: We use MAX() with a window clause (OVER (PARTITION BY ID, Name)) to create two flags:
  • has_3: Returns 1 if the user has any row where Services = 3, otherwise 0.
  • has_4: Returns 1 if the user has any row where Services = 4, otherwise 0.
    The PARTITION BY ID, Name ensures we only look at records for the same user.
• Outer CASE Statement: We map the flags to the desired SG labels:
  • If both flags are 1: The user has both services, so label as 3 & 4.
  • If only has_3 is 1: Label as 3 Only.
  • If only has_4 is 1: Label as 4 Only.

Expected Output: #

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