多字段条目下仅返回单个字段的实现方法及员工邮箱返回规则问询

Got it, let's tackle this problem where you need each employee to return exactly one email—prioritizing their staff email if it exists, otherwise falling back to their student email. Here are two practical ways to adjust your existing query, depending on how your email data is stored:

Approach 1: If Emails Are Stored as Separate Rows (e.g., one row per email type) #

If your gmal table has multiple rows per employee (one for staff email, one for student), use a window function to rank emails by priority and pick the top one:

WITH ranked_emails AS (
    SELECT 
        spriden_pidm AS pidm,
        spriden_id AS ban_id,
        spriden_last_name AS lastname,
        spriden_first_name AS firstname,
        gmal.email,
        -- Assign priority: staff emails get rank 1, student get rank 2
        ROW_NUMBER() OVER (
            PARTITION BY spriden_pidm 
            ORDER BY CASE 
                WHEN gmal.email_type = 'STAFF' THEN 1  -- Adjust this to match your actual type identifier
                ELSE 2 
            END
        ) AS email_rank,
        phone_number.area || phone_number.phone AS full_phone
        -- Add any other fields you need here
    FROM 
        spriden
        JOIN gmal ON spriden_pidm = gmal.pidm  -- Update join condition if needed
        JOIN phone_number ON spriden_pidm = phone_number.pidm
)
SELECT 
    pidm,
    ban_id,
    lastname,
    firstname,
    email,
    full_phone
    -- Include other fields here
FROM ranked_emails
WHERE email_rank = 1;  -- Only keep the highest-priority email per employee

How this works: #

• The PARTITION BY spriden_pidm groups records by each unique employee
• The ROW_NUMBER() function assigns a rank to each email for the employee, with staff emails getting the top rank
• Filtering for email_rank = 1 ensures you only get one email per employee, prioritizing staff first

If your email type is determined by domain (e.g., @staff.youruni.edu vs @student.youruni.edu), tweak the CASE statement to check the email string instead:

ORDER BY CASE WHEN gmal.email LIKE '%@staff.youruni.edu' THEN 1 ELSE 2 END

Approach 2: If Emails Are Stored as Separate Columns #

If your gmal table has separate columns for staff and student emails (e.g., staff_email and student_email), use COALESCE to pick the first non-empty value:

SELECT 
    spriden_pidm AS pidm,
    spriden_id AS ban_id,
    spriden_last_name AS lastname,
    spriden_first_name AS firstname,
    -- Prioritize staff email; if it's null, use student email
    COALESCE(gmal.staff_email, gmal.student_email) AS email,
    phone_number.area || phone_number.phone AS full_phone
    -- Add other fields here
FROM 
    spriden
    JOIN gmal ON spriden_pidm = gmal.pidm
    JOIN phone_number ON spriden_pidm = phone_number.pidm;

How this works: #

• COALESCE returns the first non-null value in the list. So if staff_email exists (isn't null), it uses that; otherwise, it falls back to student_email.

Just adjust the column names or type identifiers to match your actual database schema, and you’ll get exactly one email per employee with the correct priority.

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